CST Periodic Accrual Reconciliation

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Periodic Accrual Reconciliation Report (XML)
Short Name: CSTRACCR_XML
DB package: BOM_CSTRACCR_XMLP_PKG
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
P_chart_of_accounts_id
 
Number
Rebuild Report Information
 
LOV Oracle
Aging Number of Days
 
Number
Include Written Off Transactions
 
LOV Oracle
Dynamic Quantity Precision
 
LOV Oracle
Transaction Quantity Tolerance
 
Number
Transaction Amount Tolerance
 
Number
Include All Transactions
 
LOV Oracle
To
 
LOV Oracle
VEndors From
 
LOV Oracle
To
 
Items From
 
Period
 
LOV Oracle
Cost Group
 
LOV Oracle
Cost Type
 
LOV Oracle
Legal Entity
 
LOV Oracle
Sort By
 
LOV Oracle
Title