eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.GMF_LC_ADJ_HEADERS_V

Source

View Text - Preformatted


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')

View Text - HTML Formatted

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')