The below query will provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(receiving,inventory, WIP sources) sub-ledger modules.
--SK Receiving Source:
--SK Receiving Source:
SELECT GLCC.concatenated_segments segments
,GJL.period_name
,GJH.name journal_name
,GJB.name batch_name
,GJH.je_source journal_source
,GJH.je_category journal_category
,GLCC.segment1 entity_segment
,GLCC.segment2 project_segment
,FFV.attribute1 project_vertical_dff
,GLCC.segment3
,GLCC.segment4
,GLCC.segment5
,GLCC.segment6
,GLCC.segment7
,TO_CHAR (GJH.doc_sequence_value) gl_doc_no
,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date
,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date
,XAH.event_type_code sla_event_type
,TO_CHAR (RSH.receipt_num) document_number
,replace(PLA.item_description,'~','-') document_description
,TO_CHAR (RSH.creation_date, 'DD-MON-YYYY') document_date
-- ,APS.vendor_name party_name
-- ,APSA.vendor_site_code party_site
, (SELECT aps.segment1
FROM apps.ap_suppliers aps
WHERE aps.vendor_id = rsh.vendor_id) party_code
,(SELECT REPLACE(REPLACE(aps.vendor_name, CHR(10)), CHR(13))
FROM apps.ap_suppliers aps
WHERE aps.vendor_id = rsh.vendor_id) party_name
,(SELECT apsa.vendor_site_code
FROM apps.ap_supplier_sites_all apsa
WHERE apsa.vendor_id = rsh.vendor_id
AND apsa.vendor_site_id = rsh.vendor_site_id)
party_site
,GLCC.segment2 project_code
,NULL task_code
,NULL expenditure_type
,(SELECT pha.segment1 "po number"
FROM po_headers_all PHA,
rcv_shipment_lines RSL1,
rcv_shipment_headers RSH1
WHERE 1=1
AND PHA.po_header_id = RSL1.po_header_id
AND RSL1.shipment_header_id= RSH1.shipment_header_id
AND RSH1.shipment_header_id= RSH.shipment_header_id
AND rownum=1) po_number
,RSH.receipt_num receipt_number
,NULL item_code
,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) transaction_cur_amount
,PHA.currency_code entered_currency_code
,PHA.rate_type exchange_rate_type
,PHA.rate exchange_rate
,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) functional_currency_amount
FROM gl_je_batches GJB,
gl_je_headers GJH,
gl_je_lines GJL,
gl_import_references GIR,
gl_code_combinations_kfv GLCC,
xla_ae_headers XAH,
xla_ae_lines XAL,
xla_distribution_links XDL,
rcv_receiving_sub_ledger RSL,
rcv_transactions RT,
rcv_shipment_headers RSH,
-- rcv_shipment_lines rsl1,
po_line_locations_all PLL,
po_headers_all PHA,
po_lines_all PLA,
fnd_flex_value_sets FVS,
fnd_flex_values FFV
WHERE 1 = 1
AND GJH.je_batch_id = GJB.je_batch_id
AND GLCC.code_combination_id = GJL.code_combination_id
AND GJH.je_header_id = GJL.je_header_id
AND GJH.je_batch_id = GIR.je_batch_id
AND GJL.je_header_id = GIR.je_header_id
AND GJL.je_line_num = GIR.je_line_num
AND GIR.gl_sl_link_table = XAL.gl_sl_link_table
AND GIR.gl_sl_link_id = XAL.gl_sl_link_id
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.application_id = XAL.application_id
AND XDL.ae_header_id = XAL.ae_header_id
AND XDL.ae_line_num = XAL.ae_line_num
AND XDL.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND RSL.rcv_sub_ledger_id = XDL.source_distribution_id_num_1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RT.po_line_location_id = PLL.line_location_id
--- AND RSH.vendor_id = APS.vendor_id(+) ---commented by uday
-- AND APS.vendor_id = APSA.vendor_id
-- ANd RSH.vendor_site_id = APSA.vendor_site_id(+)
AND PLL.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLL.PO_LINE_ID
AND FFV.flex_value_set_id = FVS.flex_value_set_id(+)
AND GLCC.segment2 = FFV.flex_value(+)
AND UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
AND GJH.default_effective_date >= lc_gl_date_from
AND GJH.default_effective_date <= lc_gl_date_to
AND TRUNC (gjh.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))
AND GJH.je_source = NVL (p_gl_source, GJH.je_source)
AND GJH.je_category = NVL (p_gl_category, GJH.je_category)
AND GLCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)
AND GLCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)
AND GLCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)
AND GLCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)
AND GLCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)
AND GLCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)
AND GLCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)
-- AND GLCC.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)
AND NVL (FFV.attribute1, '-1') = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))
-==================================
Source: Inventory
Module: CM
===================================
SELECT GLCC.concatenated_segments segments
,GJL.period_name
,GJH.name journal_name
,GJB.name batch_name
,GJH.je_source journal_source
,GJH.je_category journal_category
,GLCC.segment1 entity_segment
,GLCC.segment2 project_segment
,FFV.attribute1 project_vertical_dff
,GLCC.segment3
,GLCC.segment4
,GLCC.segment5
,GLCC.segment6
,GLCC.segment7
,TO_CHAR (GJH.doc_sequence_value) gl_doc_no
,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date
,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date
,XAH.event_type_code sla_event_type
,TO_CHAR (RSH.receipt_num) document_number
,replace(PLA.item_description,'~','-') document_description
,TO_CHAR (RSH.creation_date, 'DD-MON-YYYY') document_date
,APS.vendor_name party_name
,APSA.vendor_site_code party_site
,GLCC.segment2 project_code
,NULL task_code
,NULL expenditure_type
,(SELECT pha.segment1 "po number"
FROM po_headers_all PHA,
rcv_shipment_lines RSL1,
rcv_shipment_headers RSH1
WHERE 1=1
AND PHA.po_header_id = RSL1.po_header_id
AND RSL1.shipment_header_id= RSH1.shipment_header_id
AND RSH1.shipment_header_id= RSH.shipment_header_id
AND rownum=1) po_number
,RSH.receipt_num receipt_number
,NULL item_code
,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) transaction_cur_amount
,NVL(PHA.currency_code,gjh.currency_code) entered_currency_code
,PHA.rate_type exchange_rate_type
,PHA.rate exchange_rate
,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) functional_currency_amount
,NULL reference_number
FROM gl_je_batches GJB,
gl_je_headers GJH,
gl_je_lines GJL,
gl_import_references GIR,
gl_code_combinations_kfv GLCC,
xla_ae_headers XAH,
xla_ae_lines XAL,
xla_distribution_links XDL,
wip_entities WE,
mtl_material_transactions MMT,
mtl_system_items_b MSBI,
mtl_transaction_accounts MTA,
mtl_transaction_types MTT,
fnd_flex_value_sets FVS,
fnd_flex_values FFV,
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL,
po_line_locations_all PLL,
po_headers_all PHA,
po_lines_all PLA,
ap_suppliers APS,
ap_supplier_sites_all APSA
WHERE 1 = 1
AND GJH.je_batch_id = GJB.je_batch_id
AND GLCC.code_combination_id = GJL.code_combination_id
AND GJH.je_header_id = GJL.je_header_id
AND GJH.je_batch_id = GIR.je_batch_id
AND GJL.je_header_id = GIR.je_header_id
AND GJL.je_line_num = GIR.je_line_num
AND GIR.gl_sl_link_table = XAL.gl_sl_link_table
AND GIR.gl_sl_link_id = XAL.gl_sl_link_id
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.application_id = XAL.application_id
AND XDL.ae_header_id = XAL.ae_header_id
AND XDL.ae_line_num = XAL.ae_line_num
AND XDL.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND UPPER(GJH.je_source) = 'COST MANAGEMENT'
AND UPPER(GJH.je_category) = 'INVENTORY'
AND XDL.source_distribution_id_num_1 = MTA.inv_sub_ledger_id
AND WE.wip_entity_id(+) = MMT.transaction_source_id
AND MMT.inventory_item_id = MSBI.inventory_item_id
AND MMT.organization_id = MSBI.organization_id
AND MMT.transaction_id = MTA.transaction_id
AND MMT.transaction_type_id = MTT.transaction_type_id
AND FFV.flex_value_set_id = FVS.flex_value_set_id(+)
AND GLCC.segment2 = FFV.flex_value(+)
AND UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
AND MMT.rcv_transaction_id = RT.transaction_id(+)
AND RSL.rcv_transaction_id(+) = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id(+)
AND RT.po_line_location_id = PLL.line_location_id(+)
AND PLL.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID(+)
AND PLL.PO_LINE_ID = PLA.PO_LINE_ID (+)
AND RSH.vendor_id = APS.vendor_id(+)
AND APS.vendor_id = APSA.vendor_id(+)
AND RSH.vendor_site_id = APSA.vendor_site_id(+)
AND GJH.default_effective_date >= lc_gl_date_from
AND GJH.default_effective_date <= lc_gl_date_to
AND TRUNC (gjh.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))
===============
Source: WIP
===============
SELECT GLCC.concatenated_segments segments
,GJL.period_name
,GJH.name journal_name
,GJB.name batch_name
,GJH.je_source journal_source
,GJH.je_category journal_category
,GLCC.segment1 entity_segment
,GLCC.segment2 project_segment
,FFV.attribute1 project_vertical_dff
,GLCC.segment3
,GLCC.segment4
,GLCC.segment5
,GLCC.segment6
,GLCC.segment7
,TO_CHAR (GJH.doc_sequence_value) gl_doc_no
,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date
,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date
,(SELECT NAME
FROM xla_event_classes_tl XECT
WHERE XECT.event_class_code = XDL.event_class_code
AND XECT.application_id = XDL.application_id) sla_event_class
,XAH.event_type_code sla_event_type
,(select TO_CHAR (RSH.receipt_num) from
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL
where 1=1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RSL.rcv_transaction_id = wt.rcv_transaction_id
) document_number
,(select replace(PLA.item_description,'~','-') from po_lines_all PLA
where po_line_id=wt.po_line_id
and po_header_id =wt.po_header_id ) document_description
,(select TO_CHAR (RSH.creation_date, 'DD-MON-YYYY') from
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL
where 1=1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RSL.rcv_transaction_id = wt.rcv_transaction_id
) document_date
,NULL document_status --N/A
,(select APS.segment1 from
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL,
ap_suppliers APS
where 1=1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RSL.rcv_transaction_id = wt.rcv_transaction_id
AND RSH.vendor_id = APS.vendor_id
) party_code
,(select APS.vendor_name from
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL,
ap_suppliers APS
where 1=1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RSL.rcv_transaction_id = wt.rcv_transaction_id
AND RSH.vendor_id = APS.vendor_id
) party_name
,(select APSA.vendor_site_code from
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL,
ap_suppliers APS,
ap_supplier_sites_all APSA
where 1=1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RSL.rcv_transaction_id = wt.rcv_transaction_id
AND RSH.vendor_id = APS.vendor_id
AND APS.vendor_id = APSA.vendor_id
AND RSH.vendor_site_id = APSA.vendor_site_id
) party_site
,GLCC.segment2 project_code
,NULL task_code
,NULL expenditure_type
, (SELECT pha.segment1 "po number"
FROM po_headers_all PHA where 1=1
AND PHA.po_header_id =wt.po_header_id) po_number
, ( select RSH.receipt_num from
rcv_transactions RT,
rcv_shipment_headers RSH,
rcv_receiving_sub_ledger RSL
where 1=1
AND RSL.rcv_transaction_id = RT.transaction_id
AND RT.shipment_header_id = RSH.shipment_header_id
AND RSL.rcv_transaction_id = wt.rcv_transaction_id
) receipt_number
,NULL item_code
,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) transaction_cur_amount
,nvl((SELECT PHA.currency_code
FROM po_headers_all PHA where 1=1
AND PHA.po_header_id =wt.po_header_id),gjh.currency_code) entered_currency_code
, (SELECT PHA.rate_type
FROM po_headers_all PHA where 1=1
AND PHA.po_header_id =wt.po_header_id) exchange_rate_type
, (SELECT PHA.rate
FROM po_headers_all PHA where 1=1
AND PHA.po_header_id =wt.po_header_id) exchange_rate
,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) functional_currency_amount
,NULL reference_number
FROM gl_je_batches GJB,
gl_je_headers GJH,
gl_je_lines GJL,
gl_import_references GIR,
gl_code_combinations_kfv GLCC,
xla_ae_headers XAH,
xla_ae_lines XAL,
xla_distribution_links XDL,
WIP_TRANSACTION_ACCOUNTS wta,
wip_entities WE,
wip_transactions WT,
fnd_flex_value_sets FVS,
fnd_flex_values FFV
WHERE 1 = 1
AND GJH.je_batch_id = GJB.je_batch_id
AND GLCC.code_combination_id = GJL.code_combination_id
AND GJH.je_header_id = GJL.je_header_id
AND GJH.je_batch_id = GIR.je_batch_id
AND GJL.je_header_id = GIR.je_header_id
AND GJL.je_line_num = GIR.je_line_num
AND GIR.gl_sl_link_table = XAL.gl_sl_link_table
AND GIR.gl_sl_link_id = XAL.gl_sl_link_id
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.application_id = XAL.application_id
AND XDL.ae_header_id = XAL.ae_header_id
AND XDL.ae_line_num = XAL.ae_line_num
AND XDL.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
AND UPPER(GJH.je_source) = 'COST MANAGEMENT'
AND UPPER(GJH.je_category) = 'WIP'
AND XDL.source_distribution_id_num_1 = WTA.wip_sub_ledger_id
AND WE.wip_entity_id = WTA.wip_entity_id
AND WTA.wip_entity_id = WT.wip_entity_id
AND WTA.organization_id = WT.organization_id
and WTA.transaction_id = WT.transaction_id
AND FFV.flex_value_set_id = FVS.flex_value_set_id(+)
AND GLCC.segment2 = FFV.flex_value(+)
AND UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
AND GJH.default_effective_date >= lc_gl_date_from
AND GJH.default_effective_date <= lc_gl_date_to
AND TRUNC (gjh.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))
AND GJH.je_source = NVL (p_gl_source, GJH.je_source)
AND GJH.je_category = NVL (p_gl_category, GJH.je_category)
Nice blog about punchout XML, it's being great to read this.
ReplyDeleteCXML Punchout
Please can you share same for SOURCE : Cost Management and CATEGORY : Receiving
ReplyDeleteWe are looking for similar query for Fusion cloud, appreciate if you could share it here.
ReplyDelete