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 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 gjh.je_source = gjh.je_source
AND gjh.je_category = gjh.je_category
AND nvl(ffv.attribute1,’-1′) = nvl(ffv.attribute1,’-1′);
Recent Posts
