CST Periodic WIP Distribution Details

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Periodic WIP Distribution Details Report (XML)
Short Name: CSTRPWDD_XML
DB package: BOM_CSTRPWDD_XMLP_PKG
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
Parameter Name SQL text Validation
Quantity Precision
 
LOV Oracle
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Master Organization ID
 
Number
Chart of Accounts ID
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Class Code
 
LOV Oracle
Activity
 
LOV Oracle
Department
 
LOV Oracle
Transaction Type
 
LOV Oracle
Assembly
 
Line
 
LOV Oracle
Job/Schedule
 
LOV Oracle
Organization
 
LOV Oracle
To
 
Account From
 
Period
 
LOV Oracle
Organization Cost Group
 
LOV Oracle
Cost Type
 
LOV Oracle
Legal Entity
 
LOV Oracle