CST Periodic Material and Receiving Distribution Details

Description

Categories: BI Publisher
Application: Bills of Material
Source: Periodic Material and Receiving Distribution Details Report (XML)
Short Name: CSTRPMDD_XML
DB package: BOM_CSTRPMDD_XMLP_PKG

Imported from BI Publisher
Periodic material and receiving account distributions details report

SQL 283 lines

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

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

Parameters 22

Parameter	Type	List of values SQL
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

Description

SQL 283 lines

Parameters 22

Find out more

Features

Download

FAQ