CST Periodic Accrual Reconciliation
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Periodic Accrual Reconciliation Report
Application: Bills of Material
Source: Periodic Accrual Reconciliation Report (XML)
Short Name: CSTRACCR_XML
DB package: BOM_CSTRACCR_XMLP_PKG
Description: Periodic Accrual Reconciliation Report
Application: Bills of Material
Source: Periodic Accrual Reconciliation Report (XML)
Short Name: CSTRACCR_XML
DB package: BOM_CSTRACCR_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
SELECT decode(:LP_age_days, 0, 0, floor((nvl(:P_gl_date_to,sysdate) - par.aging_date) / :LP_age_days)*:LP_age_days) Bkt_start_date , decode(:LP_age_days, 0, 0, ceil((nvl(:P_gl_date_to,sysdate) - par.aging_date) / :LP_age_days)*:LP_age_days-1) Bkt_end_date , NULL Destination , null ACCT_RAW , null ITEM_RAW , par.inventory_item_id item_id , decode(:P_sort_option, 'ITEM', nvl(msi.description,pol.item_description), NULL ) Description , par.po_unit_of_measure UOM , par.po_num PO_Num , par.vendor_name Vendor_Name , par.vendor_name_alt Vendor_Name_Alt , par.po_line_num PO_Line_Num , par.accrual_code A_Transaction , plu2.displayed_field Source , ood.organization_code Org , par.transaction_date D_Date , decode(par.transaction_source_code, 'AP', par.invoice_num, 'PO', par.receipt_num, 'INV', nvl(par.receipt_num,to_char(par.inv_transaction_id)), 'WIP', nvl(par.receipt_num,to_char(par.wip_transaction_id)) ) Document_Num , nvl(par.invoice_line_num,par.po_line_num) Lne , round(par.transaction_quantity,:P_qty_precision) T_Quantity , par.transaction_unit_price Unit_Price , round(par.transaction_amount/:round_unit) *:round_unit T_Amount , round(par.invoice_price_variance/:round_unit) *:round_unit IPV , round(par.transaction_amount/:round_unit) *:round_unit Accrual_Balance , par.write_off_id write_off_id, to_char(par.transaction_date,'DD-MON-YY') D_Date1, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_pseg', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') ACCT_PSEG, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_seg', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ACCT_SEG, /*BOM_CSTRACCR_XMLP_PKG.sort_columnformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, :ITEM_SEG, par.vendor_name, :ITEM_PSEG) Sort_column, BOM_CSTRACCR_XMLP_PKG.sort_column_altformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, :ITEM_SEG, par.vendor_name, par.vendor_name_alt, :ITEM_PSEG) Sort_column_alt, BOM_CSTRACCR_XMLP_PKG.sort_column_dspformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, :ITEM_SEG, par.vendor_name) Sort_column_dsp,*/ BOM_CSTRACCR_XMLP_PKG.item_psegformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, :ITEM_SEG, par.inventory_item_id, :ITEM_PSEG) ITEM_PSEG, DECODE(par.inventory_item_id, null, null, fnd_flex_xml_publisher_apis.process_kff_combination_1('item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) ITEM_SEG, BOM_CSTRACCR_XMLP_PKG.item_vendorformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, :ITEM_SEG, par.vendor_name) Item_Vendor, BOM_CSTRACCR_XMLP_PKG.po_line_sortformula(par.po_line_num) PO_Line_Sort, BOM_CSTRACCR_XMLP_PKG.net_accrual_balformula(round ( par.transaction_amount / :round_unit ) * :round_unit, round ( par.invoice_price_variance / :round_unit ) * :round_unit) net_accrual_bal, BOM_CSTRACCR_XMLP_PKG.indicatorformula(par.write_off_id) Indicator FROM cst_pac_accrual_reconcile_temp par , mtl_system_items msi , gl_code_combinations gcc , org_organization_definitions ood , po_lookup_codes plu2 , po_lines pol WHERE par.inventory_item_id = msi.inventory_item_id (+) AND par.po_line_id = pol.po_line_id (+) AND (msi.organization_id is null OR par.item_master_organization_id = msi.organization_id ) AND gcc.code_combination_id = par.accrual_account_id AND ood.organization_id = par.transaction_organization_id AND plu2.lookup_type = 'ACCRUAL TRX SOURCE' AND plu2.lookup_code = par.transaction_source_code AND ( ( par.vendor_name is null AND :P_vendor_from is null AND :P_vendor_to is null ) OR par.vendor_name BETWEEN nvl(:P_vendor_from, par.vendor_name) AND nvl(:P_vendor_to, par.vendor_name) ) AND par.transaction_date BETWEEN nvl(:P_gl_date_from, par.transaction_date) AND nvl(:P_gl_date_to, par.transaction_date) AND &P_ITEM_WHERE AND par.cost_type_id = :P_cost_type AND par.cost_group_id = :P_cost_group AND par.legal_entity_id = :P_legal_entity AND NOT ( :LP_WRITTEN_OFF = 'N' AND par.write_off_flag = 'Y') AND 'Y' = decode(:P_zero_balance, 'Y' , 'Y', 'N', decode(sign(abs(par.net_po_line_amount)-:P_amt_tolerance), 1, 'Y', decode(sign(abs(round(nvl(par.net_po_line_quantity,0), :P_qty_pres))-:P_qty_tolerance), 1,'Y', 'N') ) ) ORDER BY par.po_num , par.po_line_num , decode(par.transaction_source_code, 'PO',1,'INV',2,'WIP',3,'AP',4,5) , decode(par.accrual_code,'Invoice Price Var',2, 'Exchange Rate Var',3,1) , par.invoice_num , sign(par.transaction_quantity) , par.transaction_date |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Sort By |
|
LOV Oracle | |
Legal Entity |
|
LOV Oracle | |
Cost Type |
|
LOV Oracle | |
Cost Group |
|
LOV Oracle | |
Period |
|
LOV Oracle | |
Items From |
|
Char | |
To |
|
Char | |
VEndors From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Include All Transactions |
|
LOV Oracle | |
Transaction Amount Tolerance |
|
Number | |
Transaction Quantity Tolerance |
|
Number | |
Dynamic Quantity Precision |
|
LOV Oracle | |
Include Written Off Transactions |
|
LOV Oracle | |
Aging Number of Days |
|
Number |