View: GMF_LC_ADJ_HEADERS_V

Product:	GMF - Process Manufacturing Financials
Description:	View used to store LC adjustments will be populated by LCM tables
Implementation/DBA Data:	APPS.GMF_LC_ADJ_HEADERS_V

View Text

SELECT NVL(LC.PARENT_SHIP_LINE_ID
, LC.SHIP_LINE_ID) PARENT_SHIP_LINE_ID
, LC.ADJUSTMENT_NUM ADJUSTMENT_NUM
, LC.SHIP_HEADER_ID SHIP_HEADER_ID
, LC.SHIP_LINE_GROUP_ID SHIP_LINE_GROUP_ID
, LC.SHIP_LINE_ID SHIP_LINE_ID
, LC.ORGANIZATION_ID ORGANIZATION_ID
, LC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, DECODE(LC.ADJUSTMENT_NUM
, 0
, NULL
, LAG(LC.ALLOCATED_AMT) OVER(PARTITION BY RT.TRANSACTION_ID
, LC.COMPONENT_NAME
, LC.COMPONENT_TYPE ORDER BY RT.TRANSACTION_ID
, LC.COMPONENT_NAME
, LC.COMPONENT_TYPE
, LC.ADJUSTMENT_NUM)) PRIOR_LANDED_COST
, LC.ALLOCATED_AMT LANDED_COST
, LC.ALLOCATION_PERCENT ALLOCATION_PERCENT
, LC.COMPONENT_TYPE COMPONENT_TYPE
, LC.COMPONENT_NAME COMPONENT_NAME
, PE.PRICE_ELEMENT_TYPE_ID CHARGE_LINE_TYPE_ID
, PE.PRICE_ELEMENT_CODE CHARGE_LINE_TYPE_CODE
, PE.COST_ACQUISITION_CODE COST_ACQUISITION_FLAG
, DECODE(RT.LCM_ADJUSTMENT_NUM
, LC.ADJUSTMENT_NUM
, 'RCV_TRANSACTIONS'
, 'INL_SHIP_LINES') PARENT_TABLE_NAME
, DECODE(RT.LCM_ADJUSTMENT_NUM
, LC.ADJUSTMENT_NUM
, RT.TRANSACTION_ID
, LC.PARENT_SHIP_LINE_ID) PARENT_TABLE_ID
, PE.COST_COMPONENT_CLASS_ID COST_CMPNTCLS_ID
, PE.COST_ANALYSIS_CODE COST_ANALYSIS_CODE
, RT.TRANSACTION_DATE TRANSACTION_DATE
, RT.QUANTITY TRANSACTION_QUANTITY
, RT.UOM_CODE TRANSACTION_UOM_CODE
, RT.PRIMARY_QUANTITY PRIMARY_QUANTITY
, PUOM.UOM_CODE PRIMARY_UOM_CODE
, DECODE(RT.LCM_ADJUSTMENT_NUM
, LC.ADJUSTMENT_NUM
, 0
, 1) LC_ADJUSTMENT_FLAG
, RT.TRANSACTION_ID RCV_TRANSACTION_ID
, RT.TRANSACTION_TYPE RCV_TRANSACTION_TYPE
, ISH.SHIP_NUM LC_SHIP_NUM
, LC.SHIP_LINE_NUM LC_SHIP_LINE_NUM
, RSH.RECEIPT_NUM RECEIPT_NUM
, ISH.ORG_ID ORG_ID
FROM MTL_UNITS_OF_MEASURE PUOM
, PON_PRICE_ELEMENT_TYPES PE
, INL_SHIP_HEADERS_ALL ISH
, RCV_SHIPMENT_HEADERS RSH
, INL_DET_LANDED_COSTS_V LC
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_LINES RSL
WHERE PUOM.UNIT_OF_MEASURE (+) = RT.PRIMARY_UNIT_OF_MEASURE
AND PE.PRICE_ELEMENT_TYPE_ID (+) = LC.CHARGE_LINE_TYPE_ID
AND ISH.SHIP_HEADER_ID = LC.SHIP_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND NVL(LC.PARENT_SHIP_LINE_ID
, LC.SHIP_LINE_ID) = RSL.LCM_SHIPMENT_LINE_ID
AND LC.ADJUSTMENT_NUM >= RT.LCM_ADJUSTMENT_NUM
AND RT.TRANSACTION_TYPE NOT IN ('ACCEPT'
, 'MATCH'
, 'REJECT'
, 'SHIP'
, 'TRANSFER'
, 'UNORDERED'
, 'CANCEL')

Columns