VIEW: APPS.POABV_EDW_RECEIVING_TXN_FCV

Source

View Text - Preformatted

SELECT /*+ ALL_ROWS */ inc.seq_id, 1, RCV.TRANSACTION_ID ||'-'|| ELI.INSTANCE_CODE , ELI.INSTANCE_CODE , RCV.TRANSACTION_TYPE || '-' || 'RCV TRANSACTION TYPE' || '-' || 'PO' , edw_items_pkg.Item_Org_FK (POL.item_id, DECODE(POL.item_id, NULL, poh.org_id, fsp.inventory_organization_id), pol.item_description, pol.category_id, eli.instance_code) , PLT.LINE_TYPE , decode(pol.order_type_lookup_code ,'QUANTITY' ,edw_util.get_edw_base_uom(MTLU.uom_code, POL.item_id) ,'NA_EDW') , decode(pol.order_type_lookup_code ,'QUANTITY' ,edw_util.get_edw_uom(MTLU.uom_code, POL.item_id) ,'NA_EDW') , DECODE(RSH.RECEIPT_SOURCE_CODE,NULL,NULL, RSH.RECEIPT_SOURCE_CODE || '-' || 'SHIPMENT SOURCE TYPE' || '-' || 'PO') , edw_trd_partner_pkg.supplier_site_fk (poh.vendor_site_id, poh.org_id, eli.instance_code) , decode(poh.vendor_site_id, NULL, 'NA_EDW', pvs.city || '-' || pvs.zip || '-' || decode(pvs.state, null, pvs.province, pvs.state) || '-' || pvs.country) , DECODE(RCV.DESTINATION_TYPE_CODE,null, null,RCV.DESTINATION_TYPE_CODE || '-'||'RCV DESTINATION TYPE'|| '-'||'PO') , edw_organization_pkg.int_organization_fk (PLL.SHIP_TO_ORGANIZATION_ID, eli.instance_code) , decode(rcv.location_id, NULL, 'NA_EDW', decode(hrl1.location_id, NULL, EDW_GEOGRAPHY_PKG.HZ_Postcode_City_fk(rcv.location_id), hrl1.town_or_city || '-' || hrl1.postal_code || '-' || hrl1.region_2 || '-' || hrl1.country)) , decode(rcv.deliver_to_location_id, NULL, 'NA_EDW', decode(hrl2.location_id, NULL, EDW_GEOGRAPHY_PKG.HZ_Postcode_City_fk(rcv.deliver_to_location_id), hrl2.town_or_city || '-' || hrl2.postal_code || '-' || hrl2.region_2 || '-' || hrl2.country)) , DECODE(mil.physical_location_id, NULL, edw_mtl_inventory_loc_pkg.GET_LOCATOR_FK (rcv.locator_id, rcv.organization_id, rcv.subinventory), edw_mtl_inventory_loc_pkg.GET_LOCATOR_FK (mil.physical_location_id, mil.organization_id, mil.subinventory_code)) , edw_hr_person_pkg.Regular_Employee_FK (POH.AGENT_ID, eli.instance_code) , edw_hr_person_pkg.Regular_Employee_FK (RCV.DELIVER_TO_PERSON_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (RCV.TRANSACTION_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (RCV.CREATION_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (RSH.SHIPPED_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (RSH.EXPECTED_RECEIPT_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (PLL.PROMISED_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (PLL.NEED_BY_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (PLL.LAST_ACCEPT_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (PLL.CREATION_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , edw_time_pkg.cal_day_fk (RCV_PARENT.TRANSACTION_DATE, FSP.SET_OF_BOOKS_ID, eli.instance_code) , DECODE(RSH.PAYMENT_TERMS_ID,NULL,NULL, RSH.PAYMENT_TERMS_ID || 'AP' || '-' || ELI.INSTANCE_CODE) , DECODE(RRH.ROUTING_NAME,NULL,NULL, UPPER(RRH.ROUTING_NAME) || '-' || 'RCV ROUTING'|| '-' || 'ROU') , DECODE(RCV.SUBSTITUTE_UNORDERED_CODE, NULL, NULL, RCV.SUBSTITUTE_UNORDERED_CODE || '-'||'VENDOR RECEIPT OPTION' || '-' || 'PO') , decode(RCV_PARENT.TRANSACTION_TYPE, null,null, RCV_PARENT.TRANSACTION_TYPE || '-' || 'RCV TRANSACTION TYPE' || '-' || 'PO') , DECODE(RSH.FREIGHT_CARRIER_CODE,NULL,NULL,UPPER(RSH.FREIGHT_CARRIER_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG') , NVL(RCV.INSPECTION_STATUS_CODE, 'NOT INSPECTED') || '-' || 'INSPECTION STATUS' || '-' || 'PO' , DECODE(RCV.INSPECTION_QUALITY_CODE, NULL,NULL, UPPER(RCV.INSPECTION_QUALITY_CODE) || '-' || 'PO QUALITY CODE' || '-' || 'POQ') , NVL(RCV.RECEIPT_EXCEPTION_FLAG,'N') || '-' || 'YES_NO' || '-' || 'FND' , NVL(RCV.USER_ENTERED_FLAG,'N') || '-' || 'YES_NO' || '-' || 'FND' , DECODE(RCV.REASON_ID, NULL, NULL, UPPER(MTR.REASON_NAME)|| '-'|| 'TXN REASON' || '-' || 'MTR') , nvl(POH.CURRENCY_CODE, gsob.currency_code) , EDW_LOOKUP_PKG.lookup_code_fk( 'PO', 'PURCHASE CLASSIFICATION', POA_CUSTOMIZATION_PKG.Purchase_Classification_Code( RCV.transaction_id, 'RCV_TRANSACTIONS')) , EDW_SUPPLIER_ITEM_PKG.Supplier_Item_FK ( pov.vendor_name, poh.vendor_site_id, POL.VENDOR_PRODUCT_NUM) /*** Measures ***/ , decode(pol.order_type_lookup_code,'AMOUNT',null,RCV.QUANTITY * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)) , decode(pol.order_type_lookup_code,'AMOUNT',null,(RCV.QUANTITY + POA_EDW_RCV_TXNS_PKG.Qty_Corrected (RCV.TRANSACTION_ID)) * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)) , decode(pol.order_type_lookup_code,'AMOUNT',null,RSL.quantity_received * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)) , decode(rcv.transaction_type, 'RECEIVE', decode(pol.order_type_lookup_code,'AMOUNT',null,POA_EDW_RCV_TXNS_PKG.Qty_Net_Child_Txns ( RSL.SHIPMENT_LINE_ID, 'ACCEPT') * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)), NULL) , decode(rcv.transaction_type, 'RECEIVE', decode(pol.order_type_lookup_code,'AMOUNT',null,POA_EDW_RCV_TXNS_PKG.Qty_Net_Child_Txns ( RSL.SHIPMENT_LINE_ID, 'REJECT') * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)), NULL) , decode(rcv.transaction_type, 'RECEIVE', decode(pol.order_type_lookup_code,'AMOUNT',null,POA_EDW_RCV_TXNS_PKG.Qty_Net_Child_Txns ( RSL.SHIPMENT_LINE_ID, 'RETURN TO VENDOR') * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)), NULL) , decode(rcv.transaction_type, 'RECEIVE', decode(pol.order_type_lookup_code,'AMOUNT',null,POA_EDW_RCV_TXNS_PKG.Qty_Net_Child_Txns ( RSL.SHIPMENT_LINE_ID, 'RETURN TO RECEIVING') * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)), NULL) , decode(rcv.transaction_type, 'RECEIVE', decode(pol.order_type_lookup_code,'AMOUNT',null,POA_EDW_RCV_TXNS_PKG.Qty_Net_Child_Txns ( RSL.SHIPMENT_LINE_ID,'DELIVER') * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)), NULL) , decode(rcv.transaction_type, 'RECEIVE', decode(pol.order_type_lookup_code,'AMOUNT',null,POA_EDW_RCV_TXNS_PKG.Qty_Net_Child_Txns ( RSL.SHIPMENT_LINE_ID,'TRANSFER') * edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id)), NULL) , PLL.PRICE_OVERRIDE / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , DECODE(poh.rate_type, 'User', EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT ( poh.rate*PLL.PRICE_OVERRIDE, to_number(NULL), gsob.currency_code, FSP.SET_OF_BOOKS_ID, NVL(poh.rate_date, pll.creation_date), NULL), EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT ( PLL.PRICE_OVERRIDE, to_number(NULL), nvl(poh.currency_code,gsob.currency_code), FSP.SET_OF_BOOKS_ID, NVL(poh.rate_date, pll.creation_date), POH.RATE_TYPE)) / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , decode(rcv.transaction_type, 'RECEIVE', POA_EDW_RCV_TXNS_PKG.Date_Last_Delivery(RSL.SHIPMENT_LINE_ID) - rcv.transaction_date, NULL) /*** Attributes ***/ , RSH.SHIPMENT_NUM , API.INVOICE_NUM , RSH.RECEIPT_NUM || '-' || RCV.ORGANIZATION_ID , POH.SEGMENT1 , NVL(RCV.VENDOR_LOT_NUM, RSL.VENDOR_LOT_NUM) , RCV.RMA_REFERENCE , RCV.COMMENTS , RSH.COMMENTS , RSH.WAYBILL_AIRBILL_NUM , RSH.BILL_OF_LADING , NVL(RSL.PACKING_SLIP, RSH.PACKING_SLIP) /*** Others ***/ , greatest(nvl(rcv_parent.last_update_date, to_date('01/01/0001','MM/DD/YYYY')), rcv.last_update_date, nvl(rrh.last_update_date, to_date('01/01/0001','MM/DD/YYYY')), nvl(api.last_update_date, to_date('01/01/0001','MM/DD/YYYY')), nvl(mtr.last_update_date, to_date('01/01/0001','MM/DD/YYYY')), nvl(pov.last_update_date, to_date('01/01/0001','MM/DD/YYYY')), gsob.last_update_date,mtlu.last_update_date, rsl.last_update_date, rsh.last_update_date, pol.last_update_date, poh.last_update_date, plt.last_update_date, fsp.last_update_date, pll.last_update_date) , rcv.creation_date , NULL , NULL , NULL , rcv.po_distribution_id , '_DF:PO:PO_HEADERS:poh' , '_DF:PO:PO_LINES:pol' , '_DF:PO:PO_PDF_RETURN_FROM:RCV' FROM poa_edw_rcv_txns_inc inc, EDW_LOCAL_INSTANCE ELI, mtl_units_of_measure MTLU, FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_DOC_STYLE_HEADERS STYLE, AP_INVOICES_ALL API, MTL_TRANSACTION_REASONS MTR, GL_SETS_OF_BOOKS GSOB, MTL_ITEM_LOCATIONS MIL, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, PO_LINE_TYPES PLT, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, RCV_TRANSACTIONS RCV_PARENT, RCV_TRANSACTIONS RCV, PO_VENDOR_SITES_ALL PVS, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2 WHERE inc.primary_key = RCV.TRANSACTION_ID AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCV.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RCV.PARENT_TRANSACTION_ID = RCV_PARENT.TRANSACTION_ID (+) AND RCV.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND RCV.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND RCV.locator_id = mil.inventory_location_id (+) AND RCV.organization_id = mil.organization_id (+) AND RCV.INVOICE_ID = API.INVOICE_ID (+) AND RCV.REASON_ID = MTR.REASON_ID (+) and RSL.unit_of_measure = MTLU.unit_of_measure AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID AND PLL.PO_LINE_ID = POL.PO_LINE_ID AND POH.VENDOR_ID = POV.VENDOR_ID (+) AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND nvl(POH.ORG_ID, -999) = nvl(FSP.ORG_ID, -999) AND FSP.set_of_books_id = GSOB.set_of_books_id AND PVS.vendor_id (+) = POH.vendor_id AND PVS.vendor_site_id (+) = POH.vendor_site_id AND RCV.location_id = HRL1.location_id (+) AND RCV.deliver_to_location_id = HRL2.location_id (+) AND PLL.MATCHING_BASIS = 'QUANTITY' AND poh.style_id = style.style_id AND nvl(style.progress_payment_flag,'N') = 'N'

View Text - HTML Formatted

SELECT /*+ ALL_ROWS */ INC.SEQ_ID
, 1
, RCV.TRANSACTION_ID ||'-'|| ELI.INSTANCE_CODE
, ELI.INSTANCE_CODE
, RCV.TRANSACTION_TYPE || '-' || 'RCV TRANSACTION TYPE' || '-' || 'PO'
, EDW_ITEMS_PKG.ITEM_ORG_FK (POL.ITEM_ID
, DECODE(POL.ITEM_ID
, NULL
, POH.ORG_ID
, FSP.INVENTORY_ORGANIZATION_ID)
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, ELI.INSTANCE_CODE)
, PLT.LINE_TYPE
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_BASE_UOM(MTLU.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_UOM(MTLU.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, DECODE(RSH.RECEIPT_SOURCE_CODE
, NULL
, NULL
, RSH.RECEIPT_SOURCE_CODE || '-' || 'SHIPMENT SOURCE TYPE' || '-' || 'PO')
, EDW_TRD_PARTNER_PKG.SUPPLIER_SITE_FK (POH.VENDOR_SITE_ID
, POH.ORG_ID
, ELI.INSTANCE_CODE)
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY)
, DECODE(RCV.DESTINATION_TYPE_CODE
, NULL
, NULL
, RCV.DESTINATION_TYPE_CODE || '-'||'RCV DESTINATION TYPE'|| '-'||'PO')
, EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK (PLL.SHIP_TO_ORGANIZATION_ID
, ELI.INSTANCE_CODE)
, DECODE(RCV.LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL1.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(RCV.LOCATION_ID)
, HRL1.TOWN_OR_CITY || '-' || HRL1.POSTAL_CODE || '-' || HRL1.REGION_2 || '-' || HRL1.COUNTRY))
, DECODE(RCV.DELIVER_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL2.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(RCV.DELIVER_TO_LOCATION_ID)
, HRL2.TOWN_OR_CITY || '-' || HRL2.POSTAL_CODE || '-' || HRL2.REGION_2 || '-' || HRL2.COUNTRY))
, DECODE(MIL.PHYSICAL_LOCATION_ID
, NULL
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (RCV.LOCATOR_ID
, RCV.ORGANIZATION_ID
, RCV.SUBINVENTORY)
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (MIL.PHYSICAL_LOCATION_ID
, MIL.ORGANIZATION_ID
, MIL.SUBINVENTORY_CODE))
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (POH.AGENT_ID
, ELI.INSTANCE_CODE)
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (RCV.DELIVER_TO_PERSON_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RCV.TRANSACTION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RCV.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RSH.SHIPPED_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RSH.EXPECTED_RECEIPT_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.PROMISED_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.NEED_BY_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.LAST_ACCEPT_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RCV_PARENT.TRANSACTION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, DECODE(RSH.PAYMENT_TERMS_ID
, NULL
, NULL
, RSH.PAYMENT_TERMS_ID || 'AP' || '-' || ELI.INSTANCE_CODE)
, DECODE(RRH.ROUTING_NAME
, NULL
, NULL
, UPPER(RRH.ROUTING_NAME) || '-' || 'RCV ROUTING'|| '-' || 'ROU')
, DECODE(RCV.SUBSTITUTE_UNORDERED_CODE
, NULL
, NULL
, RCV.SUBSTITUTE_UNORDERED_CODE || '-'||'VENDOR RECEIPT OPTION' || '-' || 'PO')
, DECODE(RCV_PARENT.TRANSACTION_TYPE
, NULL
, NULL
, RCV_PARENT.TRANSACTION_TYPE || '-' || 'RCV TRANSACTION TYPE' || '-' || 'PO')
, DECODE(RSH.FREIGHT_CARRIER_CODE
, NULL
, NULL
, UPPER(RSH.FREIGHT_CARRIER_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG')
, NVL(RCV.INSPECTION_STATUS_CODE
, 'NOT INSPECTED') || '-' || 'INSPECTION STATUS' || '-' || 'PO'
, DECODE(RCV.INSPECTION_QUALITY_CODE
, NULL
, NULL
, UPPER(RCV.INSPECTION_QUALITY_CODE) || '-' || 'PO QUALITY CODE' || '-' || 'POQ')
, NVL(RCV.RECEIPT_EXCEPTION_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(RCV.USER_ENTERED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(RCV.REASON_ID
, NULL
, NULL
, UPPER(MTR.REASON_NAME)|| '-'|| 'TXN REASON' || '-' || 'MTR')
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'PO'
, 'PURCHASE CLASSIFICATION'
, POA_CUSTOMIZATION_PKG.PURCHASE_CLASSIFICATION_CODE( RCV.TRANSACTION_ID
, 'RCV_TRANSACTIONS'))
, EDW_SUPPLIER_ITEM_PKG.SUPPLIER_ITEM_FK ( POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POL.VENDOR_PRODUCT_NUM) /*** MEASURES ***/
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RCV.QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, (RCV.QUANTITY + POA_EDW_RCV_TXNS_PKG.QTY_CORRECTED (RCV.TRANSACTION_ID)) * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RSL.QUANTITY_RECEIVED * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'ACCEPT') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'REJECT') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'RETURN TO VENDOR') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'RETURN TO RECEIVING') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'DELIVER') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'TRANSFER') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, PLL.PRICE_OVERRIDE / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT ( POH.RATE*PLL.PRICE_OVERRIDE
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, PLL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT ( PLL.PRICE_OVERRIDE
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE_TYPE)) / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, POA_EDW_RCV_TXNS_PKG.DATE_LAST_DELIVERY(RSL.SHIPMENT_LINE_ID) - RCV.TRANSACTION_DATE
, NULL) /*** ATTRIBUTES ***/
, RSH.SHIPMENT_NUM
, API.INVOICE_NUM
, RSH.RECEIPT_NUM || '-' || RCV.ORGANIZATION_ID
, POH.SEGMENT1
, NVL(RCV.VENDOR_LOT_NUM
, RSL.VENDOR_LOT_NUM)
, RCV.RMA_REFERENCE
, RCV.COMMENTS
, RSH.COMMENTS
, RSH.WAYBILL_AIRBILL_NUM
, RSH.BILL_OF_LADING
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP) /*** OTHERS ***/
, GREATEST(NVL(RCV_PARENT.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, RCV.LAST_UPDATE_DATE
, NVL(RRH.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, NVL(API.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, NVL(MTR.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, NVL(POV.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, GSOB.LAST_UPDATE_DATE
, MTLU.LAST_UPDATE_DATE
, RSL.LAST_UPDATE_DATE
, RSH.LAST_UPDATE_DATE
, POL.LAST_UPDATE_DATE
, POH.LAST_UPDATE_DATE
, PLT.LAST_UPDATE_DATE
, FSP.LAST_UPDATE_DATE
, PLL.LAST_UPDATE_DATE)
, RCV.CREATION_DATE
, NULL
, NULL
, NULL
, RCV.PO_DISTRIBUTION_ID
, '_DF:PO:PO_HEADERS:POH'
, '_DF:PO:PO_LINES:POL'
, '_DF:PO:PO_PDF_RETURN_FROM:RCV'
FROM POA_EDW_RCV_TXNS_INC INC
, EDW_LOCAL_INSTANCE ELI
, MTL_UNITS_OF_MEASURE MTLU
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_DOC_STYLE_HEADERS STYLE
, AP_INVOICES_ALL API
, MTL_TRANSACTION_REASONS MTR
, GL_SETS_OF_BOOKS GSOB
, MTL_ITEM_LOCATIONS MIL
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_LINE_TYPES PLT
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RCV_PARENT
, RCV_TRANSACTIONS RCV
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
WHERE INC.PRIMARY_KEY = RCV.TRANSACTION_ID
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCV.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RCV.PARENT_TRANSACTION_ID = RCV_PARENT.TRANSACTION_ID (+)
AND RCV.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RCV.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND RCV.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID (+)
AND RCV.ORGANIZATION_ID = MIL.ORGANIZATION_ID (+)
AND RCV.INVOICE_ID = API.INVOICE_ID (+)
AND RCV.REASON_ID = MTR.REASON_ID (+)
AND RSL.UNIT_OF_MEASURE = MTLU.UNIT_OF_MEASURE
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND NVL(POH.ORG_ID
, -999) = NVL(FSP.ORG_ID
, -999)
AND FSP.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND PVS.VENDOR_ID (+) = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND RCV.LOCATION_ID = HRL1.LOCATION_ID (+)
AND RCV.DELIVER_TO_LOCATION_ID = HRL2.LOCATION_ID (+)
AND PLL.MATCHING_BASIS = 'QUANTITY'
AND POH.STYLE_ID = STYLE.STYLE_ID
AND NVL(STYLE.PROGRESS_PAYMENT_FLAG
, 'N') = 'N'