CST Periodic Material and Receiving Distribution Summary

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Periodic material and receiving account distributions summary report
Application: Bills of Material
Source: Periodic Material and Receiving Distribution Summary Report (XML)
Short Name: CSTRPMDS_XML
DB package: BOM_CSTRPDSS_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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, decode(rae.event_type_id,18,raet.description,
		                            19,raet.description,
					    20,raet.description,
					    raet.event_type_name), 
				     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
	      , rcv_accounting_event_types raet
              &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 raet.event_type_id = rae.event_type_id
                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
Sort by
 
LOV Oracle
Account From
 
Char
Account to
 
Char