CST Periodic Accrual Reconciliation

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