Blog / Oracle Application Blog
GL DRILL Down Query From GL into Cost Management Sub ledger Module.
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)
