CST AP and PO Accrual Reconciliation
Description
Categories: Enginatics, Kcapps
Repository: Github
Repository: Github
Imported from Concurrent Program
Application: Bills of Material
Source: AP and PO Accrual Reconciliation Report
Short Name: CSTACRAP
Application: Bills of Material
Source: AP and PO Accrual Reconciliation Report
Short Name: CSTACRAP
Run
CST AP and PO Accrual Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
with capr as ( select gsob.name ledger, haou.name operating_unit, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bal_segment', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') balancing_segment, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acc_segment', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') account_segment, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cct_segment', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_center_segment, gcc.concatenated_segments account, crc.displayed_field transaction_type, decode(capr.invoice_distribution_id, NULL, decode(capr.write_off_id, NULL, 'PO', 'WO'), 'AP') transaction_source, trunc(capr.transaction_date) transaction_date, pov.vendor_name vendor, apia.invoice_num invoice_number, aida.invoice_line_number invoice_line, nvl(poh.clm_document_number,poh.segment1) po_number, por.release_num po_release, nvl(pol.line_num_display, to_char(pol.line_num)) po_line, poll.shipment_num po_shipment, pod.distribution_num po_distribution, nvl(poll.price_override,pol.unit_price) unit_price, pod.quantity_ordered, pod.quantity_cancelled, pod.quantity_delivered, pod.quantity_billed, (select papf.full_name from per_all_people_f papf where poh.agent_id=papf.person_id and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date) buyer, (select papf.full_name from per_all_people_f papf where pod.deliver_to_person_id=papf.person_id and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date) deliver_to_person, nvl(poh.clm_document_number,poh.segment1) || nvl2(por.release_num,' (' || por.release_num || ')','') po_num_rel_ref, nvl(poh.clm_document_number,poh.segment1) || nvl2(por.release_num,' (' || por.release_num || ')','') || ' - ' || nvl(pol.line_num_display, to_char(pol.line_num)) || '/' || poll.shipment_num || '/' || pod.distribution_num po_num_rel_line_ship_dist_ref, rsh.receipt_num receipt_number, crs.po_balance po_balance, crs.ap_balance ap_balance, crs.write_off_balance wo_balance, (nvl(crs.po_balance,0) + nvl(crs.ap_balance,0) + nvl(crs.write_off_balance,0)) total_balance, gsob.currency_code currency, capr.quantity transaction_quantity, decode(capr.write_off_id, NULL, pol.unit_meas_lookup_code, NULL ) uom, capr.amount accounted_amount, capr.entered_amount entered_amount, capr.currency_code entered_currency, trunc(sysdate - decode(fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS'), 1, nvl(crs.last_receipt_date,crs.last_invoice_dist_date), greatest(nvl(crs.last_receipt_date,crs.last_invoice_dist_date), nvl(crs.last_invoice_dist_date, crs.last_receipt_date)) ) ) age_in_days, nvl2(crs.inventory_item_id, (select msiv.concatenated_segments from mtl_system_items_vl msiv where msiv.inventory_item_id = crs.inventory_item_id and msiv.organization_id = nvl(capr.inventory_organization_id,msiv.organization_id) and rownum <2 ), null) item, nvl2(crs.inventory_item_id, (select msiv.description from mtl_system_items_vl msiv where msiv.inventory_item_id = crs.inventory_item_id and msiv.organization_id = nvl(capr.inventory_organization_id,msiv.organization_id) and rownum <2 ), null) item_description, nvl2(crs.inventory_item_id, (select xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) from mtl_system_items_vl msiv where msiv.inventory_item_id = crs.inventory_item_id and msiv.organization_id = nvl(capr.inventory_organization_id,msiv.organization_id) and rownum <2 ), null) user_item_type, decode(capr.inventory_organization_id, NULL, NULL, mp.organization_code) inventory_organization, pdt.displayed_field destination, crs.po_distribution_id po_distribution_id, capr.write_off_id write_off_id, -- nvl(:p_aging_days,0) aging_days, decode(nvl(:p_aging_days,0), 0, 0, floor( ( sysdate - decode(fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS'), 1, nvl(crs.last_receipt_date,crs.last_invoice_dist_date), greatest(nvl(crs.last_receipt_date,crs.last_invoice_dist_date), nvl(crs.last_invoice_dist_date, crs.last_receipt_date)) )) / decode(nvl(:p_aging_days,0),0,1,:p_aging_days))*nvl(:p_aging_days,0)) aging_period_days_from, decode(nvl(:p_aging_days,0), 0, 0, ceil(( sysdate - decode(fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS'), 1, nvl(crs.last_receipt_date,crs.last_invoice_dist_date), greatest(nvl(crs.last_receipt_date,crs.last_invoice_dist_date), nvl(crs.last_invoice_dist_date, crs.last_receipt_date)) ) ) / decode(nvl(:p_aging_days,0),0,1,:p_aging_days))*nvl(:p_aging_days,0)-1) aging_period_days_to from cst_reconciliation_codes crc, cst_ap_po_reconciliation capr, ap_invoices_all apia, ap_invoice_distributions_all aida, mtl_parameters mp, rcv_transactions rct, rcv_shipment_headers rsh, cst_reconciliation_summary crs, po_distributions_all pod, po_line_locations_all poll, po_releases_all por, po_lines_all pol, po_headers_all poh, po_vendors pov, po_destination_types_all_v pdt, gl_code_combinations_kfv gcc, cst_accrual_accounts caa, hr_all_organization_units haou, hr_organization_information hoi, gl_sets_of_books gsob where crc.lookup_code = to_char(capr.transaction_type_code) and crc.lookup_type in ( 'RCV TRANSACTION TYPE', 'ACCRUAL WRITE-OFF ACTION','ACCRUAL TYPE') and aida.invoice_distribution_id(+) = capr.invoice_distribution_id and apia.invoice_id(+) = aida.invoice_id and mp.organization_id(+) = capr.inventory_organization_id and rct.transaction_id(+) = capr.rcv_transaction_id and rsh.shipment_header_id(+) = rct.shipment_header_id and capr.po_distribution_id = crs.po_distribution_id and crs.accrual_account_id = capr.accrual_account_id and pod.po_distribution_id = crs.po_distribution_id and poll.line_location_id = pod.line_location_id and pod.po_release_id = por.po_release_id(+) and pol.po_line_id = pod.po_line_id and poh.po_header_id = pod.po_header_id and pdt.lookup_code(+) = crs.destination_type_code and pov.vendor_id(+) = crs.vendor_id and crs.accrual_account_id = gcc.code_combination_id and crs.accrual_account_id = caa.accrual_account_id and caa.operating_unit_id = crs.operating_unit_id and capr.operating_unit_id = crs.operating_unit_id and crs.operating_unit_id = haou.organization_id and hoi.organization_id = haou.organization_id and hoi.org_information_context = 'Operating Unit Information' and gsob.set_of_books_id = to_number(hoi.org_information3) ) -- -- Main Query Starts Here select distinct capr.ledger, capr.operating_unit, capr.balancing_segment, capr.account_segment, capr.cost_center_segment, capr.account, capr.vendor, capr.po_number, capr.po_release, capr.po_line, capr.po_shipment, capr.po_distribution, capr.po_distribution_id, capr.unit_price, capr.quantity_ordered, capr.quantity_cancelled, capr.quantity_delivered, capr.quantity_billed, capr.buyer, capr.deliver_to_person, capr.po_balance, capr.ap_balance, capr.wo_balance, capr.total_balance, capr.age_in_days, capr.destination, capr.item, capr.item_description, capr.user_item_type, -- null transaction_source, null transaction_type, to_date(null) transaction_date, to_number(null) transaction_quantity, null uom, to_number(null) accounted_amount, to_number(null) entered_amount, null entered_currency, null invoice_number, to_number(null) invoice_line, null receipt_number, null inventory_organization, to_number(null) write_off_id, 'Balance' record_type, capr.po_num_rel_ref, capr.po_num_rel_line_ship_dist_ref, capr.aging_period_days_from, capr.aging_period_days_to, case nvl(:p_aging_days,0) when 0 then 'All Aging Period Days' else to_char(capr.aging_period_days_from,'999990') || ' - ' || to_char(capr.aging_period_days_to) || ' Days' end aging_period_days, decode(:p_sort_by, 'ITEM' , capr.item, 'AGE IN DAYS', decode(sign(capr.age_in_days),-1, chr(0) || translate( to_char(abs(capr.age_in_days), '000000000999.999'), '0123456789', '9876543210'), to_char(capr.age_in_days , '000000000999.999' ) ), 'VENDOR', vendor, 'TOTAL BALANCE', decode(sign(capr.total_balance),-1, chr(0) || translate( to_char(abs(capr.total_balance), '000000000999.999'), '0123456789', '9876543210'),to_char(capr.total_balance, '000000000999.999' ) ), 'PO NUMBER', capr.po_number ) sort_key from capr where 1=1 union all select capr.ledger, capr.operating_unit, capr.balancing_segment, capr.account_segment, capr.cost_center_segment, capr.account, capr.vendor, capr.po_number, capr.po_release, capr.po_line, capr.po_shipment, capr.po_distribution, capr.po_distribution_id, capr.unit_price, capr.quantity_ordered, capr.quantity_cancelled, capr.quantity_delivered, capr.quantity_billed, capr.buyer, capr.deliver_to_person, to_number(null) po_balance, to_number(null) ap_balance, to_number(null) wo_balance, to_number(null) total_balance, capr.age_in_days, capr.destination, capr.item, capr.item_description, capr.user_item_type, -- capr.transaction_source, capr.transaction_type, capr.transaction_date, capr.transaction_quantity, capr.uom, capr.accounted_amount, capr.entered_amount, capr.entered_currency, capr.invoice_number, capr.invoice_line, capr.receipt_number, capr.inventory_organization, capr.write_off_id, 'Transaction' record_type, capr.po_num_rel_ref, capr.po_num_rel_line_ship_dist_ref, capr.aging_period_days_from, capr.aging_period_days_to, case nvl(:p_aging_days,0) when 0 then 'All Aging Period Days' else to_char(capr.aging_period_days_from,'999990') || ' - ' || to_char(capr.aging_period_days_to) || ' Days' end aging_period_days, decode(:p_sort_by, 'ITEM' , capr.item, 'AGE IN DAYS', decode(sign(capr.age_in_days),-1, chr(0) || translate( to_char(abs(capr.age_in_days), '000000000999.999'), '0123456789', '9876543210'), to_char(capr.age_in_days , '000000000999.999' ) ), 'VENDOR', vendor, 'TOTAL BALANCE', decode(sign(capr.total_balance),-1, chr(0) || translate( to_char(abs(capr.total_balance), '000000000999.999'), '0123456789', '9876543210'),to_char(capr.total_balance, '000000000999.999' ) ), 'PO NUMBER', capr.po_number ) sort_key from capr where 1=1 and :p_show_transactions = 'Y' order by sort_key, po_number, po_release, po_line, po_shipment, po_distribution, record_type |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Show Transaction Details |
|
LOV Oracle | |
Balancing Segment From |
|
LOV | |
Balancing Segment To |
|
LOV | |
Accrual Account |
|
LOV | |
Aging Period Days |
|
Number | |
Minimum Age in Days |
|
Number | |
Maximum Age in Days |
|
Number | |
Minimum Outstanding Balance |
|
Number | |
Maximum Outstanding Balance |
|
Number | |
Item |
|
LOV | |
Vendor |
|
LOV | |
PO Number |
|
LOV | |
PO Release |
|
LOV | |
PO Line |
|
Number | |
PO Shipment |
|
Number | |
Destination Type |
|
LOV | |
Sort By |
|
LOV Oracle |