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
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 NameSQL textValidation
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
Value From
 
Number
To 4
 
Number
Transaction Source Type
 
LOV Oracle
Source From
 
Char
To 5
 
Char
Transaction Type 2
 
LOV Oracle
Transaction Reason
 
LOV Oracle
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Download
Blitz Report™