CST Periodic Material and Receiving Distribution Details
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
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 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 | ||
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 |