CST Periodic WIP Distribution Summary

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