CST Period Close Reconciliation

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Period Close Reconciliation Report
Application: Bills of Material
Source: Period Close Reconciliation Report (XML)
Short Name: CSTRPCRE_XML
DB package: BOM_CSTRPCRE_XMLP_PKG
SELECT
CPCS.subinventory_code sub_code,
DECODE(CPCS.subinventory_code,
              NULL, :P_NULLSUB,
              SUB.description) sub_description,
fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') sub_item,
round(SUM(CPCS.accounted_value) * (:P_EXCHANGE_RATE) / :P_ROUND_UNIT) * :P_ROUND_UNIT sub_acc_val,
round(SUM(CPCS.rollback_value) * (:P_EXCHANGE_RATE) / :P_ROUND_UNIT) * :P_ROUND_UNIT sub_onh_val,
round(SUM(CPCS.rollback_value - CPCS.accounted_value) * (:P_EXCHANGE_RATE) / :P_ROUND_UNIT) * :P_ROUND_UNIT sub_adj_val
FROM
mtl_system_items_b_kfv MSI,
mtl_secondary_inventories SUB,
&P_SOURCE_TABLE CPCS
WHERE	MSI.organization_id = :p_org_id
AND	SUB.organization_id(+) = :p_org_id
AND	CPCS.inventory_item_id = MSI.inventory_item_id
AND	CPCS.subinventory_code = SUB.secondary_inventory_name(+)
AND	MSI.concatenated_segments
	BETWEEN	NVL(:P_ITEM_FROM, MSI.concatenated_segments)
	AND	NVL(:P_ITEM_TO, MSI.concatenated_segments)
AND         NVL(CPCS.subinventory_code,-1)
                BETWEEN NVL(:P_SUB_FROM,  NVL(CPCS.subinventory_code,-1))
                AND          NVL(:P_SUB_TO, NVL(CPCS.subinventory_code,-1))
AND	&P_CPCS_WHERE
GROUP
BY	CPCS.subinventory_code,
                DECODE(CPCS.subinventory_code,
                               NULL, :P_NULLSUB,
                               SUB.description),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')
&P_SUB_SORT_TEXT
Parameter Name SQL text Validation
Sort Option
 
LOV Oracle
Period
 
LOV Oracle
Run To Date
 
Date
Cost Group From
 
LOV Oracle
Cost Group To
 
LOV Oracle
Subinventory From
 
LOV Oracle
Subinventory To
 
LOV Oracle
Item From
 
Char
Item To
 
Char
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle