Link Between AR,SLA and GL

SELECT b.NAME batch_name,
                 b.description batch_description,
                 h.je_category,
                 h.je_source,
                 h.period_name je_period_name,
                 h.NAME journal_name,
                 h.status journal_status,
                 h.description je_description,
                 l.je_line_num line_number   
FROM gl_je_batches b,
             gl_je_headers h,
             gl_je_lines l,
             gl_code_combinations_kfv glcc,
             gl_import_references gir,
             xla_ae_lines xlal,
             xla_ae_headers xlah,
             xla_events xlae,
             xla.xla_transaction_entities xlate,
             ra_customer_trx_all rct,
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =':P_TRX_NUMBER


Ur's
AmarAlam

Link Between AP,SLA and GL

SELECT   c.code_combination_id, h.je_header_id, l.ae_header_id, l.ae_line_num,
         te.source_id_int_1, te.application_id, te.entity_id, h.je_source,
         h.je_category, i.gl_date, s.vendor_name, s.segment1 AS supplier_no,
         l.event_class_code AS event_class, i.invoice_id,
         ad.invoice_distribution_id, i.invoice_num AS transaction_number,
         i.invoice_date, INITCAP (jl.description) description,
         jl.accounted_dr AS debit, jl.accounted_cr AS credit,
         NVL (jl.accounted_dr, 0), NVL (jl.accounted_cr, 0) net_amount
    FROM apps.gl_je_headers h,
         apps.gl_je_lines jl,
         apps.gl_code_combinations c,
         apps.gl_import_references r,
         apps.xla_ae_lines al,
         apps.xla_ae_headers ah,
         apps.xla_distribution_links l,
         apps.ap_invoices_all i,
         apps.ap_invoice_distributions_all ad,
         apps.ap_suppliers s,
         apps.xla_events e,
         apps.xla_transaction_entities te
   WHERE ad.accounting_date BETWEEN :startdate AND :enddate
     AND c.code_combination_id = 6429
       AND ad.line_type_lookup_code = ‘item’
     AND jl.je_header_id = h.je_header_id
     AND jl.code_combination_id = c.code_combination_id
     AND al.gl_sl_link_id = r.gl_sl_link_id
     AND al.ae_header_id = ah.ae_header_id
     AND al.application_id = ah.application_id
     AND ah.application_id = e.application_id
     AND ah.event_id = e.event_id
     AND e.application_id = te.application_id(+)
     AND e.entity_id = te.entity_id(+)
     AND r.je_header_id = jl.je_header_id
     AND r.je_line_num = jl.je_line_num
     AND l.ae_header_id = al.ae_header_id
     AND l.ae_line_num = al.ae_line_num
     AND l.applied_to_source_id_num_1 = i.invoice_id
     AND l.source_distribution_id_num_1 = ad.invoice_distribution_id
     AND ad.invoice_id = i.invoice_id
     AND i.vendor_id = s.vendor_id
ORDER BY i.gl_date DESC


Ur's
AmarAlam

Oracle FNDLOAD Scripts To Migrate AOL Objects

Following are the usage list of FNDLOAD to upload and download various AOL objects.

Concurrent Program
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XXCUST_CP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXCUST_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XXCUST_LKP"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct  XXCUST_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Message
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="XXCUST_MESG%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Request Set and Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_RS.ldt REQ_SET REQUEST_SET_NAME='XXCUST_RS'

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XXCUST_LNK'


FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XXCUST_LNK.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Form Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt FUNCTION FUNC_APP_SHORT_NAME='XXCUST' FUNCTION_NAME='XXCUST_FUNC'

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt

Profile

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_PROF.ldt PROFILE PROFILE_NAME="XXCUST_PROFILE" APPLICATION_SHORT_NAME="XXCUST"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_FUNC.ldt

Menu

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt

Data Definition

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXCUST_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXCUST DATA_SOURCE_CODE=XXCUST_DS

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_DD.ldt


Ur's
AmarAlam

Oracle Order to Cash Queries

Query to Join OM and requisition Interface table for Back 2 back Order

SELECT l.line_id, l.flow_status_code , l.open_flag,pr.interface_source_code,pr.interface_source_line_id,pr.note_to_buyer,
pr.note_to_receiver
FROM
oe_order_lines_all l,
po_requisitions_interface_all pr
WHERE l.line_id = pr.interface_source_line_id
AND pr.interface_source_code='CTO'

Query to Join OM and Purchase Order tables for Back 2 Back Order

SELECT ph.segment1,a. supply_source_line_id, a.supply_source_header_id
FROM
mtl_reservations a,
oe_order_lines_all l,
po_headers_all ph
WHERE demand_source_line_id = &Enter_Order_lineID
AND l.line_id = a.demand_source_line_id
AND a.supply_source_header_id = ph.po_header_id

Query to Join OM and PO Requisition table for Back 2 Back Order

SELECT ph.segment1,a. supply_source_line_id, a.supply_source_header_id
FROM
mtl_reservations a,
oe_order_lines_all l,
po_requisition_headers_all pqh
WHERE demand_source_line_id = &Enter_Order_lineID
AND l.line_id = a.demand_source_line_id
AND a.supply_source_header_id = pqh.requisition_header_id

Query to Join OM , WSH and AR table

SELECT h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,
wdd.delivery_detail_id,wnd.delivery_id,wdd.shipped_quantity,a.org_id,
a.creation_date ,a.trx_number,b.quantity_ordered , b.quantity_invoiced ,b.interface_line_attribute1,b.interface_line_attribute3,
b.interface_line_attribute6,interface_line_attribute12
FROM
ra_customer_trx_all a,
ra_customer_trx_lines_all b,
oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE a.customer_trx_id = b.customer_trx_id
AND a.interface_header_context = 'ORDER ENTRY'
AND b.interface_line_attribute1 = to_char(h.order_number)
AND h.header_id = l.header_id
AND to_char(l.line_id) = b.interface_line_attribute6
AND l.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND to_char(wnd.delivery_id) = b.interface_line_attribute3

Mapping Between AR and OM (Transaction Flex field)
(RAL) - RA_CUSTOMER_TRX_LINES_ALL

RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num
RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type
RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery ID
RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill
RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID
RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading
RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID =
RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID


Ur's
AmarAlam

Sachin Tendulkar (Thank You Sachin).....!

I'm not "The God Of Cricket!! God is not a simple word, It means everything, I'm just a cricketer, God is only one & He is compareless, Request to Fans: Please do not compare me with God. 
--Sachin Tendulker #Respect.

Thank You Sachin.

Ur's
Amaralam

How to get the Distinct Values in TABLE Value set?

Method 1

Create the VIEW based on DISTINCT values; use the VIEW for creates the VALUESET.

Method 2

Paste the QUERY in TABLE field with alias name, and give the column name (with alias name).

(For an example):

TABLE: (SELECT  DISTINCT W.NAME,W.TRIP_ID,W.STATUS_NAME,WV.SOURCE_CODE,
WV.RELEASED_STATUS,WV.ORGANIZATION_ID,WV.INV_INTERFACED_FLAG FROM APPS.WSH_DELIVERABLE_TRIPS_V W,APPS.WSH_DELIVERABLES_V WV WHERE W.DELIVERY_DETAIL_ID=WV.DELIVERY_DETAIL_ID) Z

Value: Z.NAME
Id: Z.TRIP_ID

Ur's
AmarAlam