VIEW: APPS.POABV_EDW_AGREEMENT_LINES_FCV

Source

View Text - Preformatted

SELECT inc.seq_id, 1, to_char(pol.po_line_id) || '-' || eli.instance_code , eli.instance_code , edw_hr_person_pkg.Regular_Employee_FK (poh.agent_id, eli.instance_code) , edw_hr_person_pkg.Regular_Employee_FK (NVL(POA_EDW_SPEND_PKG.APPROVED_BY(poh.po_header_id), poh.agent_id), eli.instance_code) , 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) , edw_trd_partner_pkg.supplier_site_fk (poh.vendor_site_id, poh.org_id, eli.instance_code) , edw_organization_pkg.operating_unit_fk (poh.org_id, eli.instance_code) , edw_time_pkg.cal_day_fk( poh.creation_date, fsp.set_of_books_id, eli.instance_code) , edw_time_pkg.cal_day_fk( pol.creation_date, fsp.set_of_books_id, eli.instance_code) , decode(NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pol.creation_date, poh.po_header_id), poh.approved_date), NULL, NULL, edw_time_pkg.cal_day_fk( NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pol.creation_date, poh.po_header_id), poh.approved_date), fsp.set_of_books_id, eli.instance_code)) , decode(poh.revised_date, NULL, NULL, edw_time_pkg.cal_day_fk( to_date(poh.revised_date, 'dd-MON-rr HH24:MI'), fsp.set_of_books_id, eli.instance_code)) , decode(poh.start_date, NULL, NULL, edw_time_pkg.cal_day_fk( poh.start_date, fsp.set_of_books_id, eli.instance_code)) , decode(poh.end_date, NULL, NULL, edw_time_pkg.cal_day_fk( poh.end_date, fsp.set_of_books_id, eli.instance_code)) , decode(poh.acceptance_due_date, NULL, NULL, edw_time_pkg.cal_day_fk( poh.acceptance_due_date, fsp.set_of_books_id, eli.instance_code)) , decode(poh.rate_date, NULL, NULL, edw_time_pkg.cal_day_fk( poh.rate_date, fsp.set_of_books_id, eli.instance_code)) , decode(poh.printed_date, NULL, NULL, edw_time_pkg.cal_day_fk( poh.printed_date, fsp.set_of_books_id, eli.instance_code)) , decode(poh.terms_id, NULL, NULL, (poh.terms_id || '-' || 'AP' || '-' || eli.instance_code)) , decode(poh.closed_code, NULL, NULL, (upper(poh.closed_code) || '-' || 'DOCUMENT STATE' || '-' || 'PO')) , decode(poh.type_lookup_code, NULL, NULL, (upper(poh.type_lookup_code) || '-' || 'AGREEMENT TYPE' || '-' || 'POD')) , decode(poh.ship_via_lookup_code, NULL, NULL, (upper(poh.ship_via_lookup_code) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG')) , decode(poh.fob_lookup_code, NULL, NULL, (upper(poh.fob_lookup_code) || '-' || 'FOB' || '-' || 'PO')) , decode(poh.freight_terms_lookup_code, NULL, NULL, (upper(poh.freight_terms_lookup_code) || '-' || 'FREIGHT TERMS' || '-' || 'PO')) , decode(pol.transaction_reason_code, NULL, NULL, (upper(pol.transaction_reason_code) || '-' || 'TRANSACTION REASON' || '-' || 'PO')) , decode(pol.price_type_lookup_code, NULL, NULL, (upper(pol.price_type_lookup_code) || '-' || 'PRICE TYPE' || '-' || 'PO')) , decode(pol.price_break_lookup_code, NULL, NULL, (upper(pol.price_break_lookup_code) || '-' || 'PRICE BREAK TYPE' || '-' || 'PO')) , nvl(poh.acceptance_required_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.frozen_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.approved_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.user_hold_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.confirming_order_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.supply_agreement_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pol.negotiated_by_preparer_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pol.cancel_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(pol.closed_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , nvl(poh.edi_processed_flag, 'N') || '-' || 'YES_NO' || '-' || 'FND' , 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(poh.bill_to_location_id, NULL, 'NA_EDW', hrl2.town_or_city || '-' || hrl2.postal_code || '-' || hrl2.region_2 || '-' || hrl2.country) , decode(poh.ship_to_location_id, NULL, 'NA_EDW', decode(hrl1.location_id, null, EDW_GEOGRAPHY_PKG.HZ_Postcode_City_fk(poh.ship_to_location_id), hrl1.town_or_city || '-' || hrl1.postal_code || '-' || hrl1.region_2 || '-' || hrl1.country)) , decode(poh.vendor_site_id, NULL, 'NA_EDW', pvs.city || '-' || pvs.zip || '-' || decode(pvs.state, null, pvs.province, pvs.state) || '-' || pvs.country) , NVL(poh.currency_code, gsob.currency_code) , poh.rate_type , plt.line_type , decode(poh.start_date, least(poh.start_date, sysdate), decode(poh.end_date, greatest(poh.end_date,sysdate), 'Y', decode(poh.end_date, NULL, 'Y', 'N')), decode(poh.start_date, NULL, decode(poh.end_date, greatest(poh.end_date,sysdate), 'Y', decode(poh.end_date, NULL, 'Y', 'N')), 'N')) || '-' || 'YES_NO' || '-' || 'FND' , EDW_SUPPLIER_ITEM_PKG.Supplier_Item_FK ( pov.vendor_name, poh.vendor_site_id, POL.VENDOR_PRODUCT_NUM) , pol.committed_amount , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*pol.committed_amount, to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( pol.committed_amount, to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,POL.QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE ,POL.ITEM_ID) ,NULL) , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,POL.MIN_ORDER_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE ,POL.ITEM_ID) ,NULL) , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,POL.MAX_ORDER_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE ,POL.ITEM_ID) ,NULL) , pol.market_price / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*pol.market_price, to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( pol.market_price, to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , pol.not_to_exceed_price / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*pol.not_to_exceed_price, to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( pol.not_to_exceed_price, to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , pol.list_price_per_unit / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*pol.list_price_per_unit, to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( pol.list_price_per_unit, to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , pol.unit_price / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*pol.unit_price, to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( pol.unit_price, to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) / edw_util.get_uom_conv_rate (MTLU.uom_code, POL.item_id) , pol.min_release_amount , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*pol.min_release_amount, to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( pol.min_release_amount, to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,POL.QUANTITY_COMMITTED * EDW_UTIL.GET_UOM_CONV_RATE ( MTLU.UOM_CODE , POL.ITEM_ID) ,NULL) , DECODE(POL.ORDER_TYPE_LOOKUP_CODE ,'QUANTITY' ,POA_EDW_SPEND_PKG.LINE_QTY_RELEASED( POL.PO_HEADER_ID , POL.ORG_ID , POL.PO_LINE_ID) * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE ,POL.ITEM_ID) ,NULL) , POA_EDW_SPEND_PKG.LINE_AMT_RELEASED( pol.po_header_id, pol.org_id, pol.po_line_id) , DECODE(poh.rate_type, 'User', edw_currency.convert_global_amount( poh.rate*POA_EDW_SPEND_PKG.LINE_AMT_RELEASED (pol.po_header_id, pol.org_id, pol.po_line_id), to_number(NULL), gsob.currency_code, fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), NULL), edw_currency.convert_global_amount( POA_EDW_SPEND_PKG.LINE_AMT_RELEASED (pol.po_header_id, pol.org_id, pol.po_line_id), to_number(NULL), nvl(poh.currency_code, gsob.currency_code), fsp.set_of_books_id, NVL(poh.rate_date, pol.creation_date), poh.rate_type)) , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pol.creation_date, poh.po_header_id), poh.approved_date) - pol.creation_date , nvl(poh.printed_date, poh.approved_date) - poh.approved_date , poh.acceptance_due_date - nvl(poh.printed_date, poh.approved_date) , pol.item_id , pol.item_description , NVL(pol.note_to_vendor, poh.note_to_vendor) , poh.comments , poh.note_to_receiver , pol.po_header_id , pol.po_line_id , pol.cancel_reason , greatest(poh.last_update_date, pol.last_update_date, nvl(pov.last_update_date, to_date('01/01/0001','MM/DD/YYYY')), fsp.last_update_date, plt.last_update_date, gsob.last_update_date,mtlu.last_update_date) , NULL , NULL , NULL , decode(edw_currency.get_rate( decode(poh.rate_type, 'User', gsob.currency_code, nvl(poh.currency_code, gsob.currency_code)), NVL(poh.rate_date, pol.creation_date), NULL), -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY', 'LOCAL READY') collection_status , '_DF:PO:PO_HEADERS:poh' , '_DF:PO:PO_LINES:pol' FROM poa_edw_alines_inc inc, po_lines_all pol, po_headers_all poh, po_vendors pov, mtl_units_of_measure mtlu, gl_sets_of_books gsob, po_line_types plt, financials_system_params_all fsp, edw_local_instance eli, po_vendor_sites_all pvs, hr_locations_all hrl1, hr_locations_all hrl2 WHERE inc.primary_key = pol.po_line_id and pol.po_header_id = poh.po_header_id and pol.line_type_id = plt.line_type_id and pol.unit_meas_lookup_code = mtlu.unit_of_measure(+) and poh.vendor_id = pov.vendor_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 poh.ship_to_location_id = hrl1.location_id (+) and poh.bill_to_location_id = hrl2.location_id (+)

View Text - HTML Formatted

SELECT INC.SEQ_ID
, 1
, TO_CHAR(POL.PO_LINE_ID) || '-' || ELI.INSTANCE_CODE
, ELI.INSTANCE_CODE
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (POH.AGENT_ID
, ELI.INSTANCE_CODE)
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (NVL(POA_EDW_SPEND_PKG.APPROVED_BY(POH.PO_HEADER_ID)
, POH.AGENT_ID)
, ELI.INSTANCE_CODE)
, 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)
, EDW_TRD_PARTNER_PKG.SUPPLIER_SITE_FK (POH.VENDOR_SITE_ID
, POH.ORG_ID
, ELI.INSTANCE_CODE)
, EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK (POH.ORG_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK( POH.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK( POL.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, DECODE(NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_POH(POL.CREATION_DATE
, POH.PO_HEADER_ID)
, POH.APPROVED_DATE)
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_POH(POL.CREATION_DATE
, POH.PO_HEADER_ID)
, POH.APPROVED_DATE)
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.REVISED_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( TO_DATE(POH.REVISED_DATE
, 'DD-MON-RR HH24:MI')
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.START_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.START_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.END_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.END_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.ACCEPTANCE_DUE_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.ACCEPTANCE_DUE_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.RATE_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.RATE_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.PRINTED_DATE
, NULL
, NULL
, EDW_TIME_PKG.CAL_DAY_FK( POH.PRINTED_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE))
, DECODE(POH.TERMS_ID
, NULL
, NULL
, (POH.TERMS_ID || '-' || 'AP' || '-' || ELI.INSTANCE_CODE))
, DECODE(POH.CLOSED_CODE
, NULL
, NULL
, (UPPER(POH.CLOSED_CODE) || '-' || 'DOCUMENT STATE' || '-' || 'PO'))
, DECODE(POH.TYPE_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.TYPE_LOOKUP_CODE) || '-' || 'AGREEMENT TYPE' || '-' || 'POD'))
, DECODE(POH.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.SHIP_VIA_LOOKUP_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG'))
, DECODE(POH.FOB_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.FOB_LOOKUP_CODE) || '-' || 'FOB' || '-' || 'PO'))
, DECODE(POH.FREIGHT_TERMS_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.FREIGHT_TERMS_LOOKUP_CODE) || '-' || 'FREIGHT TERMS' || '-' || 'PO'))
, DECODE(POL.TRANSACTION_REASON_CODE
, NULL
, NULL
, (UPPER(POL.TRANSACTION_REASON_CODE) || '-' || 'TRANSACTION REASON' || '-' || 'PO'))
, DECODE(POL.PRICE_TYPE_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POL.PRICE_TYPE_LOOKUP_CODE) || '-' || 'PRICE TYPE' || '-' || 'PO'))
, DECODE(POL.PRICE_BREAK_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POL.PRICE_BREAK_LOOKUP_CODE) || '-' || 'PRICE BREAK TYPE' || '-' || 'PO'))
, NVL(POH.ACCEPTANCE_REQUIRED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.FROZEN_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.APPROVED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.USER_HOLD_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.CONFIRMING_ORDER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.SUPPLY_AGREEMENT_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POL.NEGOTIATED_BY_PREPARER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POL.CANCEL_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POL.CLOSED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.EDI_PROCESSED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, 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(POH.BILL_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, HRL2.TOWN_OR_CITY || '-' || HRL2.POSTAL_CODE || '-' || HRL2.REGION_2 || '-' || HRL2.COUNTRY)
, DECODE(POH.SHIP_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL1.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(POH.SHIP_TO_LOCATION_ID)
, HRL1.TOWN_OR_CITY || '-' || HRL1.POSTAL_CODE || '-' || HRL1.REGION_2 || '-' || HRL1.COUNTRY))
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, POH.RATE_TYPE
, PLT.LINE_TYPE
, DECODE(POH.START_DATE
, LEAST(POH.START_DATE
, SYSDATE)
, DECODE(POH.END_DATE
, GREATEST(POH.END_DATE
, SYSDATE)
, 'Y'
, DECODE(POH.END_DATE
, NULL
, 'Y'
, 'N'))
, DECODE(POH.START_DATE
, NULL
, DECODE(POH.END_DATE
, GREATEST(POH.END_DATE
, SYSDATE)
, 'Y'
, DECODE(POH.END_DATE
, NULL
, 'Y'
, 'N'))
, 'N')) || '-' || 'YES_NO' || '-' || 'FND'
, EDW_SUPPLIER_ITEM_PKG.SUPPLIER_ITEM_FK ( POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POL.VENDOR_PRODUCT_NUM)
, POL.COMMITTED_AMOUNT
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POL.COMMITTED_AMOUNT
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POL.COMMITTED_AMOUNT
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE))
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, POL.QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, NULL)
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, POL.MIN_ORDER_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, NULL)
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, POL.MAX_ORDER_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, NULL)
, POL.MARKET_PRICE / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POL.MARKET_PRICE
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POL.MARKET_PRICE
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE)) / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, POL.NOT_TO_EXCEED_PRICE / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POL.NOT_TO_EXCEED_PRICE
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POL.NOT_TO_EXCEED_PRICE
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE)) / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, POL.LIST_PRICE_PER_UNIT / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POL.LIST_PRICE_PER_UNIT
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POL.LIST_PRICE_PER_UNIT
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE)) / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, POL.UNIT_PRICE / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POL.UNIT_PRICE
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POL.UNIT_PRICE
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE)) / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, POL.MIN_RELEASE_AMOUNT
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POL.MIN_RELEASE_AMOUNT
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POL.MIN_RELEASE_AMOUNT
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE))
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, POL.QUANTITY_COMMITTED * EDW_UTIL.GET_UOM_CONV_RATE ( MTLU.UOM_CODE
, POL.ITEM_ID)
, NULL)
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, POA_EDW_SPEND_PKG.LINE_QTY_RELEASED( POL.PO_HEADER_ID
, POL.ORG_ID
, POL.PO_LINE_ID) * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, NULL)
, POA_EDW_SPEND_PKG.LINE_AMT_RELEASED( POL.PO_HEADER_ID
, POL.ORG_ID
, POL.PO_LINE_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POH.RATE*POA_EDW_SPEND_PKG.LINE_AMT_RELEASED (POL.PO_HEADER_ID
, POL.ORG_ID
, POL.PO_LINE_ID)
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( POA_EDW_SPEND_PKG.LINE_AMT_RELEASED (POL.PO_HEADER_ID
, POL.ORG_ID
, POL.PO_LINE_ID)
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, POH.RATE_TYPE))
, NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_POH(POL.CREATION_DATE
, POH.PO_HEADER_ID)
, POH.APPROVED_DATE) - POL.CREATION_DATE
, NVL(POH.PRINTED_DATE
, POH.APPROVED_DATE) - POH.APPROVED_DATE
, POH.ACCEPTANCE_DUE_DATE - NVL(POH.PRINTED_DATE
, POH.APPROVED_DATE)
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, NVL(POL.NOTE_TO_VENDOR
, POH.NOTE_TO_VENDOR)
, POH.COMMENTS
, POH.NOTE_TO_RECEIVER
, POL.PO_HEADER_ID
, POL.PO_LINE_ID
, POL.CANCEL_REASON
, GREATEST(POH.LAST_UPDATE_DATE
, POL.LAST_UPDATE_DATE
, NVL(POV.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, FSP.LAST_UPDATE_DATE
, PLT.LAST_UPDATE_DATE
, GSOB.LAST_UPDATE_DATE
, MTLU.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, DECODE(EDW_CURRENCY.GET_RATE( DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POH.RATE_DATE
, POL.CREATION_DATE)
, NULL)
, -1
, 'RATE NOT AVAILABLE'
, -2
, 'INVALID CURRENCY'
, 'LOCAL READY') COLLECTION_STATUS
, '_DF:PO:PO_HEADERS:POH'
, '_DF:PO:PO_LINES:POL'
FROM POA_EDW_ALINES_INC INC
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_VENDORS POV
, MTL_UNITS_OF_MEASURE MTLU
, GL_SETS_OF_BOOKS GSOB
, PO_LINE_TYPES PLT
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, EDW_LOCAL_INSTANCE ELI
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
WHERE INC.PRIMARY_KEY = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MTLU.UNIT_OF_MEASURE(+)
AND POH.VENDOR_ID = POV.VENDOR_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 POH.SHIP_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND POH.BILL_TO_LOCATION_ID = HRL2.LOCATION_ID (+)