CST Periodic Material and Receiving Distribution Details

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Periodic material and receiving account distributions details report
Application: Bills of Material
Source: Periodic Material and Receiving Distribution Details Report (XML)
Short Name: CSTRPMDD_XML
DB package: BOM_CSTRPMDD_XMLP_PKG
Run CST Periodic Material and Receiving Distribution Details and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
 mmt.transaction_date                   Txn_date
, &P_acct_flex         C_acct_flex2
, &P_item_flex          C_item_flex2
, msi.description                        Description
, &C_type_option       Type
, mmt.transaction_source_type_id         Type_id 
, mmt.organization_id                             Org_id
, DECODE(mmt.transaction_source_type_id,
                1,to_char(mmt.transaction_source_id),
                2,&P_so_flex,
                3,&P_gl_flex,
                5,to_char(mmt.transaction_source_id),
                6,&P_alias_flex,
                7,to_char(mmt.transaction_source_id),
                8,&P_so_flex,
                9,to_char(mmt.transaction_source_id),
               10,to_char(mmt.transaction_source_id),
               11,to_char(mmt.transaction_source_id),
               12,&P_so_flex,
                mmt.transaction_source_name)          Source
, decode(cal.accounted_dr,NULL,-1*mmt.periodic_primary_quantity,mmt.periodic_primary_quantity) Quantity
, msi.primary_uom_code               Primary_uom
, round(cal.rate_or_amount * :P_EXCHANGE_RATE , :cp_ext_precision)   Cost
, nvl(nvl(cal.accounted_dr,-1*cal.accounted_cr),0) * :P_EXCHANGE_RATE      Value 
,cal.ae_line_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field2, 
	BOM_CSTRPMDD_XMLP_PKG.c_item_value2_rformula(:C_item_value2) C_item_value2_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field2, 
	BOM_CSTRPMDD_XMLP_PKG.c_quantity2formula(cal.ae_line_id, decode ( cal.accounted_dr , NULL , - 1 * mmt.periodic_primary_quantity , mmt.periodic_primary_quantity )) C_Quantity2, 
		DECODE(
       mmt.transaction_source_type_id,
       2, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') ,
       3, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source1', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ,
       6, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source2', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE') ,
       8, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') ,
       12, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') ,
       BOM_CSTRPMDD_XMLP_PKG.C_SOURCEFORMULA(mmt.organization_id, mmt.transaction_source_type_id , mmt.transaction_source_id )  
    )  C_Source2, 
	BOM_CSTRPMDD_XMLP_PKG.value2_rformula(nvl ( nvl ( cal.accounted_dr , - 1 * cal.accounted_cr ) , 0 ) * :P_EXCHANGE_RATE) Value2_R
FROM
 cst_ae_headers              cah
, mtl_material_transactions   mmt
&C_from_cat
, mtl_system_items            msi
, MTL_SALES_ORDERS            mkts
, MTL_GENERIC_DISPOSITIONS    mdsp
, gl_code_combinations glc
&C_from_type
, cst_ae_lines                cal
, gl_code_combinations        gcc
&C_source_from
WHERE cah.ae_header_id = cal.ae_header_id
  AND cah.accounting_event_id = mmt.transaction_id
  AND cah.acct_event_source_table = 'MMT' 
  AND cal.code_combination_id = gcc.code_combination_id    
  AND mmt.inventory_item_id = msi.inventory_item_id
  AND msi.organization_id  =( SELECT NVL(organization_id,-1)
                              FROM   cst_cost_groups
                              WHERE  cost_group_id = :P_cost_group_id)
  AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
  AND nvl(mmt.owning_tp_type,2) = 2
  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 &P_where_acct
  AND &P_where_item
  AND &P_where_cat
  &P_source_where2
  &C_where_type
  &C_where_cat
  &C_source_where
  &C_where_value
  &C_where_reason
  AND (mmt.transaction_source_type_id = 
                  :P_source_type_id OR :P_source_type_id IS NULL) 
  AND (mmt.transaction_type_id = 
                 :P_txn_type_id OR :P_txn_type_id IS NULL)
  AND mmt.transaction_source_id = mkts.sales_order_id(+)
  AND mmt.transaction_source_id = mdsp.disposition_id(+)
  AND mmt.transaction_source_id = glc.code_combination_id(+)
UNION
SELECT
 mmt.transaction_date                   Txn_date
, &P_acct_flex         C_acct_flex2
, &P_item_flex          C_item_flex2
, msi.description                        Description
, &C_type_option       Type
, mmt.transaction_source_type_id         Type_id 
, mmt.organization_id                             Org_id
, DECODE(mmt.transaction_source_type_id,
                1,to_char(mmt.transaction_source_id),
                2,&P_so_flex,
                3,&P_gl_flex,
                5,to_char(mmt.transaction_source_id),
                6,&P_alias_flex,
                7,to_char(mmt.transaction_source_id),
                8,&P_so_flex,
                9,to_char(mmt.transaction_source_id),
               10,to_char(mmt.transaction_source_id),
               11,to_char(mmt.transaction_source_id),
               12,&P_so_flex,
                mmt.transaction_source_name)          Source
, decode(cal.accounted_dr,NULL,-1*mmt.periodic_primary_quantity,mmt.periodic_primary_quantity) Quantity
, msi.primary_uom_code               Primary_uom
, round(cal.rate_or_amount * :P_EXCHANGE_RATE , :cp_ext_precision)   Cost
, nvl(nvl(cal.accounted_dr,-1*cal.accounted_cr),0) * :P_EXCHANGE_RATE      Value 
,cal.ae_line_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field2, 
	BOM_CSTRPMDD_XMLP_PKG.c_item_value2_rformula(:C_item_value2) C_item_value2_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field2, 
	BOM_CSTRPMDD_XMLP_PKG.c_quantity2formula(cal.ae_line_id, decode ( cal.accounted_dr , NULL , - 1 * mmt.periodic_primary_quantity , mmt.periodic_primary_quantity )) C_Quantity2, 
		DECODE(
       mmt.transaction_source_type_id,
       2, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') ,
       3, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source1', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ,
       6, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source2', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE') ,
       8, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') ,
       12, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE') ,
       BOM_CSTRPMDD_XMLP_PKG.C_SOURCEFORMULA(mmt.organization_id, mmt.transaction_source_type_id , mmt.transaction_source_id )  
    )  C_Source2,  
	BOM_CSTRPMDD_XMLP_PKG.value2_rformula(nvl ( nvl ( cal.accounted_dr , - 1 * cal.accounted_cr ) , 0 ) * :P_EXCHANGE_RATE) Value2_R
FROM
 cst_ae_headers              cah
, mtl_material_transactions   mmt
&C_from_cat
, mtl_system_items            msi
, MTL_SALES_ORDERS            mkts
, MTL_GENERIC_DISPOSITIONS    mdsp
, gl_code_combinations glc
&C_from_type
, cst_ae_lines                cal
, gl_code_combinations        gcc
&C_source_from
WHERE cah.ae_header_id = cal.ae_header_id
  AND cah.accounting_event_id = mmt.transaction_id
  AND cah.acct_event_source_table = 'MMT' 
  AND cal.code_combination_id = gcc.code_combination_id    
  AND mmt.inventory_item_id = msi.inventory_item_id
  AND msi.organization_id  =( SELECT NVL(organization_id,-1)
                              FROM   cst_cost_groups
                              WHERE  cost_group_id = :P_cost_group_id)
  AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
  AND nvl(mmt.owning_tp_type,2) = 2
  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 &P_where_acct
  AND &P_where_item
  AND &P_where_cat
  &P_source_where2
  &C_where_type
  &C_where_cat
  &C_source_where
  &C_where_value
  &C_where_reason
  AND (mmt.transaction_source_type_id = 
                  :P_source_type_id OR :P_source_type_id IS NULL) 
  AND (mmt.transaction_type_id = 
                 :P_txn_type_id OR :P_txn_type_id IS NULL)
  AND mmt.transaction_source_id = mkts.sales_order_id(+)
  AND mmt.transaction_source_id = mdsp.disposition_id(+)
  AND mmt.transaction_source_id = glc.code_combination_id(+)
UNION ALL
SELECT
  rt.transaction_date                    Txn_date
, &P_acct_flex         C_acct_flex2
, &P_item_flex             C_item_flex2
, msi.description                        Description
, &C_rt_type_option               Type
, 0                                   Type_id 
, 0                                 Org_id
, null                              Source
, decode(cal.accounted_dr,NULL,-1*rt.primary_quantity,rt.primary_quantity) Quantity
, msi.primary_uom_code             Primary_uom
, round(cal.rate_or_amount * :P_EXCHANGE_RATE , :cp_ext_precision)   Cost
, nvl(nvl(cal.accounted_dr,-1*cal.accounted_cr),0) * :P_EXCHANGE_RATE    Value 
,cal.ae_line_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field2, 
	BOM_CSTRPMDD_XMLP_PKG.c_item_value2_rformula(:C_item_value2) C_item_value2_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field2, 
	--BOM_CSTRPMDD_XMLP_PKG.c_quantity2formula(cal.ae_line_id, decode ( cal.accounted_dr , NULL , - 1 * mmt.primary_quantity , mmt.primary_quantity )) C_Quantity2, 
	null C_Quantity2,
	null C_Source2, 
	BOM_CSTRPMDD_XMLP_PKG.value2_rformula(nvl ( nvl ( cal.accounted_dr , - 1 * cal.accounted_cr ) , 0 ) * :P_EXCHANGE_RATE) Value2_R
FROM
  cst_ae_headers              cah
, cst_ae_lines                cal
, mtl_system_items            msi  
, rcv_transactions            rt
, rcv_shipment_lines          rsl
, gl_code_combinations        gcc
&C_from_cat
WHERE cah.ae_header_id = cal.ae_header_id
  AND cah.accounting_event_id = rt.transaction_id
  AND cah.acct_event_source_table = 'RT' 
  AND cal.code_combination_id = gcc.code_combination_id 
  AND rt.shipment_line_id = rsl.shipment_line_id
  AND NVL(rt.consigned_flag,'N') = 'N'
  AND rsl.item_id = msi.inventory_item_id(+)
  AND cah.organization_id = msi.organization_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 &P_where_acct
  AND &P_where_item
  AND &P_where_cat
  &C_rt_where_cat
  &C_where_value
  &C_rt_where_reason
UNION ALL 
SELECT
  rae.transaction_date                    Txn_date
, &P_acct_flex         C_acct_flex2
, &P_item_flex             C_item_flex2
, msi.description                        Description
,decode(:P_type_option,1,rt.source_document_code,
                       decode(rae.event_type_id,18,raet.description,
                                                19,raet.description,
                                                20,raet.description,
                                                raet.event_type_name))  Type
, 0                                   Type_id 
, 0                                 Org_id
, null                              Source
, decode(cal.accounted_dr,NULL,-1*rae.primary_quantity,rae.primary_quantity) Quantity
, msi.primary_uom_code             Primary_uom
, round(cal.rate_or_amount * :P_EXCHANGE_RATE , :cp_ext_precision)   Cost
, nvl(nvl(cal.accounted_dr,-1*cal.accounted_cr),0) * :P_EXCHANGE_RATE    Value 
,cal.ae_line_id, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_pad2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') C_item_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_field2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_field2, 
	BOM_CSTRPMDD_XMLP_PKG.c_item_value2_rformula(:C_item_value2) C_item_value2_R, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_pad2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'PADDED_VALUE') C_acct_pad2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acct_field2', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_acct_field2, 
	--BOM_CSTRPMDD_XMLP_PKG.c_quantity2formula(cal.ae_line_id, decode ( cal.accounted_dr , NULL , - 1 * mmt.primary_quantity , mmt.primary_quantity )) C_Quantity2, 
	null C_Quantity2,
	null C_Source2, 
	BOM_CSTRPMDD_XMLP_PKG.value2_rformula(nvl ( nvl ( cal.accounted_dr , - 1 * cal.accounted_cr ) , 0 ) * :P_EXCHANGE_RATE) Value2_R
FROM
  cst_ae_headers              cah
, cst_ae_lines                cal
, mtl_system_items            msi  
, rcv_transactions            rt
, rcv_accounting_events       rae
, rcv_shipment_lines          rsl
, gl_code_combinations        gcc
, rcv_accounting_event_types raet
&C_from_cat
WHERE cah.ae_header_id = cal.ae_header_id
  AND cah.accounting_event_id = rae.accounting_event_id
  AND rae.rcv_transaction_id = rt.transaction_id
  AND cah.acct_event_source_table = 'RAE' 
  AND cal.code_combination_id = gcc.code_combination_id 
  AND rt.shipment_line_id = rsl.shipment_line_id
  AND NVL(rt.consigned_flag,'N') = 'N'
  AND rsl.item_id = msi.inventory_item_id(+)
  AND cah.organization_id = msi.organization_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 raet.event_type_id = rae.event_type_id
  AND &P_where_acct
  AND &P_where_item
  AND &P_where_cat
  &C_rt_where_cat
  &C_where_value
  &C_rt_where_reason
ORDER BY 
    2 asc
  , 1 asc
  , 3 asc
  , 5 asc
  , 7 asc
  , 8 asc
  , 9 asc
  , 11 asc
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
Transaction Type 
 
 LOV Oracle
Account From 
 
 Char
To 
 
 Char
Category Set 
 
 LOV Oracle
Category From 
 
 Char
To 2 
 
 Char
Item From 
 
 Char
To 3 
 
 Char