CST Periodic WIP Distribution Summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Periodic WIP account distributions summary report
Application: Bills of Material
Source: Periodic WIP Distribution Summary Report (XML)
Short Name: CSTRPWDS_XML
DB package: BOM_CSTRPDSS_XMLP_PKG
Run CST Periodic WIP Distribution Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
             -- Account
             Sort_option
            , sum(c_debits) sum_debits
            , sum(c_credits) sum_credits
            , sum(c_debits)-sum(c_credits) Net_activity,
                C_acct_pad,
                C_acct_field,
             --   BOM_CSTRPDSS_XMLP_PKG.cf_dr_acct_tot_rformula(:C_dr_acct_tot) CF_dr_acct_tot_r,
              --  BOM_CSTRPDSS_XMLP_PKG.cf_cr_acct_tot_rformula(:C_cr_acct_tot) CF_cr_acct_tot_r,
             --   BOM_CSTRPDSS_XMLP_PKG.cf_acct_tot_rformula(:C_acct_tot) CF_acct_tot_r,
            Sort_option C_item_field_1,
                BOM_CSTRPDSS_XMLP_PKG.cf_sum_debits_rformula(sum ( c_debits )) CF_sum_debits_r,
                BOM_CSTRPDSS_XMLP_PKG.cf_sum_credits_rformula(sum ( c_credits )) CF_sum_credits_r,
                BOM_CSTRPDSS_XMLP_PKG.cf_net_activity_rformula(sum ( c_debits ) - sum ( c_credits )) CF_net_activity_r
            FROM
            ( SELECT
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field
              , decode(:p_sort_id, 1, &C_item_field1, &p_sort_option) Sort_option
			  , nvl(cal.accounted_dr,0) c_debits
              , nvl(cal.accounted_cr,0) c_credits
              , cal.ae_line_id
              FROM
                cst_ae_lines cal
              , cst_ae_headers cah
              , gl_code_combinations gcc
              , mtl_material_transactions mmt
              &p_mat_table_from
              WHERE :p_wip = 2
                AND cal.ae_header_id = cah.ae_header_id
                AND cal.code_combination_id = gcc.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 cah.accounting_event_id = mmt.transaction_id
                AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
                AND nvl(mmt.owning_tp_type,2) = 2
                AND cah.acct_event_source_table = 'MMT'
                AND &plex_account_where
                &p_mat_where
              UNION
            SELECT
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field
              , decode(:p_sort_id, 1, &C_item_field1,  2, rt.transaction_type, 3, rt.source_document_code) Sort_option
              , nvl(cal.accounted_dr,0) c_debits
              , nvl(cal.accounted_cr,0) c_credits
              , cal.ae_line_id
            FROM
                cst_ae_lines cal
              , cst_ae_headers cah
              , gl_code_combinations gcc
              , rcv_transactions rt
              &p_rcv_table_from
              WHERE :p_wip = 2
                AND cal.ae_header_id = cah.ae_header_id
                AND cal.code_combination_id = gcc.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 cah.accounting_event_id = rt.transaction_id
                AND NVL(rt.consigned_flag, 'N') = 'N'
                AND cah.acct_event_source_table = 'RT'
                AND &plex_account_where
                &p_rcv_where
              UNION
            SELECT
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field
              , decode(:p_sort_id, 1, &C_item_field1,  2, rt.transaction_type, 3, rt.source_document_code) Sort_option
			  , nvl(cal.accounted_dr,0) c_debits
              , nvl(cal.accounted_cr,0) c_credits
              , cal.ae_line_id
            FROM
                cst_ae_lines cal
              , cst_ae_headers cah
              , gl_code_combinations gcc
              , rcv_transactions rt
              , rcv_accounting_events rae
              &p_rcv_table_from
              WHERE :p_wip = 2
                AND cal.ae_header_id = cah.ae_header_id
                AND cal.code_combination_id = gcc.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 cah.accounting_event_id = rae.accounting_event_id
                AND rae.rcv_transaction_id = rt.transaction_id
                AND NVL(rt.consigned_flag, 'N') = 'N'
                AND cah.acct_event_source_table = 'RAE'
                AND &plex_account_where
                &p_rcv_where
            UNION
            SELECT
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field
              , ml.meaning Sort_option
              , nvl(cal.accounted_dr,0) c_debits
              , nvl(cal.accounted_cr,0) c_credits
              , cal.ae_line_id
            FROM
                cst_ae_lines cal
              , cst_ae_headers cah
              , gl_code_combinations gcc
              , wip_transactions wt
              , mfg_lookups ml
              WHERE :p_wip = 1
                AND cal.ae_header_id = cah.ae_header_id
                AND cal.code_combination_id = gcc.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 cah.accounting_event_id = wt.transaction_id
                AND cah.acct_event_source_table = 'WT'
                AND ml.lookup_code = wt.transaction_type
                AND ml.lookup_type = 'WIP_TRANSACTION_TYPE_SHORT'
                AND &plex_account_where
            )
            GROUP BY
             -- Account
          --  ,
		   C_acct_pad,
                C_acct_field,
			Sort_option
          /*  Order By
               1
              ,2*/
			  order by 5,6,10,4
Parameter Name SQL text Validation
Legal Entity
 
LOV Oracle
Cost Type
 
LOV Oracle
Organization Cost Group
 
LOV Oracle
Period
 
LOV Oracle
Account From
 
Char
Account to
 
Char
Blitz Report™