CST Periodic WIP Distribution Details

SELECT
              null C_FLEXDATA
            , l.description Description
            , wt.transaction_date Txn_date
            , wt.operation_seq_num OpSq
            , wt.resource_seq_num RsSq
            , we.wip_entity_name Job_Schedule
            , wl.line_code Line
            , msi.description A_Desc
            , wdj.class_code D_class
            , wri.class_code R_class
            , bd.department_code Department
            , br.resource_code Res_Ovhd
            , br.unit_of_measure UOM
            , ca.activity Activity
            , lu.meaning Txn_type
            , xlu.meaning Basis
            , decode(cal.accounted_dr,NULL, -1 * wt.primary_quantity,wt.primary_quantity) Primary_quantity
            , round(cal.rate_or_amount * :p_exchange_rate, :p_rate_precision) Rate_or_Amt
            , nvl(nvl(cal.accounted_dr, -1 * cal.accounted_cr),0) * :p_exchange_rate Extended_cost
            , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_sort', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEX_SORT
            , fnd_flex_xml_publisher_apis.process_kff_combination_1('account', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') Account
            , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_descrip', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_ACCT_DESCRIP
            , BOM_CSTRPWDD_XMLP_PKG.c_acct_total_rformula(:C_ACCT_TOTAL) C_acct_total_r
            , BOM_CSTRPWDD_XMLP_PKG.class(wri.class_code, wdj.class_code) CLASS
            , fnd_flex_xml_publisher_apis.process_kff_combination_1('assembly', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ASSEMBLY
            , BOM_CSTRPWDD_XMLP_PKG.c_job_total_rformula(:C_JOB_TOTAL) C_job_total_r
            , BOM_CSTRPWDD_XMLP_PKG.c_ext_cost_rformula(nvl ( nvl ( cal.accounted_dr , - 1 * cal.accounted_cr ) , 0 ) * :p_exchange_rate) extended_cost_r
            FROM
              gl_code_combinations L
            , wip_entities we
            , wip_lines wl
            , wip_discrete_jobs wdj
            , wip_repetitive_items wri
            , mtl_system_items msi
            , mfg_lookups lu
            , mfg_lookups xlu
            , cst_activities ca
            , bom_departments bd
            , bom_resources br
            , cst_ae_headers cah
            , cst_ae_lines cal
            , wip_transactions wt
            WHERE cah.ae_header_id=cal.ae_header_id
              AND cah.accounting_event_id=wt.transaction_id
              AND UPPER(cah.acct_event_source_table)='WT'
              AND cal.code_combination_id=l.code_combination_id
              AND cah.cost_group_id=:p_cost_group_id
              AND cah.cost_type_id=:p_cost_type_id
              AND cah.period_id=:p_period_id
              AND wt.transaction_type=nvl(:p_trans_type,wt.transaction_type)
              AND nvl(wt.activity_id,-1)=nvl(:p_activity,nvl(wt.activity_id,-1))
              AND nvl(wt.department_id,-1)=nvl(:p_department,nvl(wt.department_id,-1))
              AND nvl(wt.line_id,-1)=nvl(:p_line,nvl(wt.line_id,-1))
              AND wt.organization_id=cah.organization_id
              AND we.organization_id=wt.organization_id
              AND we.wip_entity_id=wt.wip_entity_id
              AND nvl(we.primary_item_id,-1)=nvl(:p_assembly_id,nvl(we.primary_item_id,-1))
              AND wdj.organization_id(+)=wt.organization_id
              AND wdj.wip_entity_id(+)=wt.wip_entity_id
              AND wri.organization_id(+)=wt.organization_id
              AND wri.wip_entity_id(+)=wt.wip_entity_id
              AND wri.line_id(+)=wt.line_id
              AND wl.organization_id(+)=wt.organization_id
              AND wl.line_id(+)=wt.line_id
              AND msi.organization_id(+)=we.organization_id
              AND msi.inventory_item_id(+)=we.primary_item_id
              AND bd.department_id(+)=wt.department_id
              AND br.resource_id(+)=cal.resource_id
              AND ca.activity_id(+)=cal.activity_id
              AND lu.lookup_type='WIP_TRANSACTION_TYPE_SHORT'
              AND lu.lookup_code=wt.transaction_type
              AND xlu.lookup_type='CST_BASIS_SHORT'
              AND xlu.lookup_code=cal.basis_type
              AND &p_limit_entity
              AND &p_where_acct
              AND &p_limit_classes
            ORDER BY
            fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_sort', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE')
            , fnd_flex_xml_publisher_apis.process_kff_combination_1('account', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')
            , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_descrip', 'SQLGL', 'GL#', L.CHART_OF_ACCOUNTS_ID, NULL, L.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION')
            , we.wip_entity_name
            , wl.line_code
            , wt.transaction_date
            , wt.transaction_type
            , wt.operation_seq_num
            , wt.resource_seq_num
            , br.resource_code
            , wt.creation_date
            , wt.transaction_id
            , cal.rowid