VIEW: APPS.PO_SGD_MOD_HEADERS_V

Source

View Text - Preformatted

SELECT ph_po_header_id pk1_value, ph_draft_id pk2_value, null pk3_value, null pk4_value, null pk5_value, col_name , col_value, decode(col_name, 'ACCEPTANCE_REQUIRED_FLAG', (select displayed_field from po_lookup_codes where lookup_code = col_value and lookup_type = 'ACCEPTANCE_REQUIRED'), 'CLM_CONTRACT_OFFICER', (select full_name from per_all_people_f where person_id = col_value and trunc(sysdate) between effective_start_date and effective_end_date), 'USER_DOCUMENT_STATUS', (select displayed_field from po_lookup_codes where lookup_code = col_value and lookup_type = 'PO_USER_DOCUMENT_STATUS'), 'VENDOR_SITE_ID', (select vendor_site_code from po_vendor_sites_all where vendor_site_id = col_value), 'VENDOR_CONTACT_ID', (select full_name from po_supplier_contacts_val_v psc, po_headers_draft_all phd where psc.vendor_contact_id = col_value and psc.vendor_site_id = phd.vendor_site_id and phd.po_header_id = ph_po_header_id and phd.draft_id = ph_draft_id), 'SUPPLIER_NOTIF_METHOD', (select displayed_field from po_lookup_codes where lookup_code = col_value and lookup_type = 'DOCUMENT_COMMUNICATION_METHOD'), 'AGENT_ID', (select full_name from per_all_people_f where person_id = col_value and trunc(sysdate) between effective_start_date and effective_end_date), 'CLM_AWARD_ADMINISTRATOR', (select full_name from per_all_people_f where person_id = col_value and trunc(sysdate) between effective_start_date and effective_end_date), 'CONFIRMING_ORDER_FLAG', (select meaning from fnd_lookups where lookup_code = col_value and lookup_type = 'YES_NO'), 'TERMS_ID', (select name from ap_terms where term_id = col_value), 'SHIP_TO_LOCATION_ID', (select location_code from hr_locations_all where location_id = col_value), 'FREIGHT_TERMS_LOOKUP_CODE', (select displayed_field from po_lookup_codes where lookup_code = col_value and lookup_type = 'FREIGHT TERMS' ), 'SHIPPING_CONTROL', (select displayed_field from po_lookup_codes where lookup_code = col_value and lookup_type = 'SHIPPING CONTROL'), 'SHIP_VIA_LOOKUP_CODE', (select nvl(ofc.freight_code_tl, col_value) from org_freight_tl ofc where ofc.freight_code = col_value and ofc.organization_id = org_id and ofc.language = userenv('lang') ), 'FOB_LOOKUP_CODE', (select displayed_field from po_lookup_codes where lookup_type = 'FOB' and lookup_code = col_value), null) col_desc from ( SELECT ph.po_header_id ph_po_header_id, ph.draft_id ph_draft_id, ph.org_id, to_char(po_datatemplate_pkg.get_header_amount_ordered(ph.po_header_id, ph.draft_id)) total_amount, ph.acceptance_required_flag, to_char(ph.acceptance_due_date) acceptance_due_date, to_char(ph.clm_contract_officer) clm_contract_officer, ph.user_document_status, to_char(ph.vendor_site_id) vendor_site_id, to_char(ph.vendor_contact_id) vendor_contact_id, ph.supplier_notif_method, ph.fax, ph.email_address, ph.clm_external_idv, ph.clm_vendor_offer_number, to_char(ph.clm_effective_date) clm_effective_date, to_char(ph.agent_id) agent_id, to_char(ph.clm_award_administrator) clm_award_administrator, ph.comments, ph.confirming_order_flag, to_char(ph.terms_id) terms_id, to_char(ph.clm_no_signed_copies_to_return) clm_no_signed_copies_to_return, to_char(ph.ship_to_location_id) ship_to_location_id, ph.ship_via_lookup_code, ph.freight_terms_lookup_code, ph.fob_lookup_code, ph.shipping_control, ph.note_to_vendor, ph.note_to_receiver, to_char(ph.blanket_total_amount) blanket_total_amount, to_char(ph.price_update_tolerance) price_update_tolerance, to_char(ph.start_date) start_date, to_char(ph.end_date) end_date, to_char(ph.amount_limit) amount_limit, to_char(ph.clm_amount_released) clm_amount_released, to_char(ph.clm_max_order_amount) clm_max_order_amount, to_char(ph.clm_min_order_amount) clm_min_order_amount, to_char(ph.clm_min_guarantee_award_amt) clm_min_guarantee_award_amt, to_char(ph.clm_min_guar_award_amt_percent) clm_min_guar_award_amt_percent, ph.change_status FROM po_headers_draft_all ph WHERE ph.po_header_id = PO_GEN_DIFF_PKG.getModPK1 and ph.draft_id = PO_GEN_DIFF_PKG.getModPK2 ) po_header_unpivot_data unpivot include nulls ( col_value for col_name in ( TOTAL_AMOUNT, ACCEPTANCE_REQUIRED_FLAG, ACCEPTANCE_DUE_DATE, CLM_CONTRACT_OFFICER, USER_DOCUMENT_STATUS, VENDOR_SITE_ID, VENDOR_CONTACT_ID, SUPPLIER_NOTIF_METHOD, FAX, EMAIL_ADDRESS, CLM_EXTERNAL_IDV, CLM_VENDOR_OFFER_NUMBER, CLM_EFFECTIVE_DATE, AGENT_ID, CLM_AWARD_ADMINISTRATOR, COMMENTS, CONFIRMING_ORDER_FLAG, TERMS_ID, CLM_NO_SIGNED_COPIES_TO_RETURN, SHIP_TO_LOCATION_ID, SHIP_VIA_LOOKUP_CODE, FREIGHT_TERMS_LOOKUP_CODE, FOB_LOOKUP_CODE, SHIPPING_CONTROL, NOTE_TO_VENDOR, NOTE_TO_RECEIVER, BLANKET_TOTAL_AMOUNT, PRICE_UPDATE_TOLERANCE, START_DATE, END_DATE, AMOUNT_LIMIT, CLM_AMOUNT_RELEASED, CLM_MAX_ORDER_AMOUNT, CLM_MIN_ORDER_AMOUNT, CLM_MIN_GUARANTEE_AWARD_AMT, CLM_MIN_GUAR_AWARD_AMT_PERCENT, CHANGE_STATUS ) )

View Text - HTML Formatted

SELECT PH_PO_HEADER_ID PK1_VALUE
, PH_DRAFT_ID PK2_VALUE
, NULL PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, COL_NAME
, COL_VALUE
, DECODE(COL_NAME
, 'ACCEPTANCE_REQUIRED_FLAG'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'ACCEPTANCE_REQUIRED')
, 'CLM_CONTRACT_OFFICER'
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = COL_VALUE
AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE)
, 'USER_DOCUMENT_STATUS'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_USER_DOCUMENT_STATUS')
, 'VENDOR_SITE_ID'
, (SELECT VENDOR_SITE_CODE
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_SITE_ID = COL_VALUE)
, 'VENDOR_CONTACT_ID'
, (SELECT FULL_NAME
FROM PO_SUPPLIER_CONTACTS_VAL_V PSC
, PO_HEADERS_DRAFT_ALL PHD
WHERE PSC.VENDOR_CONTACT_ID = COL_VALUE
AND PSC.VENDOR_SITE_ID = PHD.VENDOR_SITE_ID
AND PHD.PO_HEADER_ID = PH_PO_HEADER_ID
AND PHD.DRAFT_ID = PH_DRAFT_ID)
, 'SUPPLIER_NOTIF_METHOD'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'DOCUMENT_COMMUNICATION_METHOD')
, 'AGENT_ID'
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = COL_VALUE
AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE)
, 'CLM_AWARD_ADMINISTRATOR'
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = COL_VALUE
AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE)
, 'CONFIRMING_ORDER_FLAG'
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'YES_NO')
, 'TERMS_ID'
, (SELECT NAME
FROM AP_TERMS
WHERE TERM_ID = COL_VALUE)
, 'SHIP_TO_LOCATION_ID'
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE LOCATION_ID = COL_VALUE)
, 'FREIGHT_TERMS_LOOKUP_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'FREIGHT TERMS' )
, 'SHIPPING_CONTROL'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'SHIPPING CONTROL')
, 'SHIP_VIA_LOOKUP_CODE'
, (SELECT NVL(OFC.FREIGHT_CODE_TL
, COL_VALUE)
FROM ORG_FREIGHT_TL OFC
WHERE OFC.FREIGHT_CODE = COL_VALUE
AND OFC.ORGANIZATION_ID = ORG_ID
AND OFC.LANGUAGE = USERENV('LANG') )
, 'FOB_LOOKUP_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'FOB'
AND LOOKUP_CODE = COL_VALUE)
, NULL) COL_DESC
FROM ( SELECT PH.PO_HEADER_ID PH_PO_HEADER_ID
, PH.DRAFT_ID PH_DRAFT_ID
, PH.ORG_ID
, TO_CHAR(PO_DATATEMPLATE_PKG.GET_HEADER_AMOUNT_ORDERED(PH.PO_HEADER_ID
, PH.DRAFT_ID)) TOTAL_AMOUNT
, PH.ACCEPTANCE_REQUIRED_FLAG
, TO_CHAR(PH.ACCEPTANCE_DUE_DATE) ACCEPTANCE_DUE_DATE
, TO_CHAR(PH.CLM_CONTRACT_OFFICER) CLM_CONTRACT_OFFICER
, PH.USER_DOCUMENT_STATUS
, TO_CHAR(PH.VENDOR_SITE_ID) VENDOR_SITE_ID
, TO_CHAR(PH.VENDOR_CONTACT_ID) VENDOR_CONTACT_ID
, PH.SUPPLIER_NOTIF_METHOD
, PH.FAX
, PH.EMAIL_ADDRESS
, PH.CLM_EXTERNAL_IDV
, PH.CLM_VENDOR_OFFER_NUMBER
, TO_CHAR(PH.CLM_EFFECTIVE_DATE) CLM_EFFECTIVE_DATE
, TO_CHAR(PH.AGENT_ID) AGENT_ID
, TO_CHAR(PH.CLM_AWARD_ADMINISTRATOR) CLM_AWARD_ADMINISTRATOR
, PH.COMMENTS
, PH.CONFIRMING_ORDER_FLAG
, TO_CHAR(PH.TERMS_ID) TERMS_ID
, TO_CHAR(PH.CLM_NO_SIGNED_COPIES_TO_RETURN) CLM_NO_SIGNED_COPIES_TO_RETURN
, TO_CHAR(PH.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PH.SHIPPING_CONTROL
, PH.NOTE_TO_VENDOR
, PH.NOTE_TO_RECEIVER
, TO_CHAR(PH.BLANKET_TOTAL_AMOUNT) BLANKET_TOTAL_AMOUNT
, TO_CHAR(PH.PRICE_UPDATE_TOLERANCE) PRICE_UPDATE_TOLERANCE
, TO_CHAR(PH.START_DATE) START_DATE
, TO_CHAR(PH.END_DATE) END_DATE
, TO_CHAR(PH.AMOUNT_LIMIT) AMOUNT_LIMIT
, TO_CHAR(PH.CLM_AMOUNT_RELEASED) CLM_AMOUNT_RELEASED
, TO_CHAR(PH.CLM_MAX_ORDER_AMOUNT) CLM_MAX_ORDER_AMOUNT
, TO_CHAR(PH.CLM_MIN_ORDER_AMOUNT) CLM_MIN_ORDER_AMOUNT
, TO_CHAR(PH.CLM_MIN_GUARANTEE_AWARD_AMT) CLM_MIN_GUARANTEE_AWARD_AMT
, TO_CHAR(PH.CLM_MIN_GUAR_AWARD_AMT_PERCENT) CLM_MIN_GUAR_AWARD_AMT_PERCENT
, PH.CHANGE_STATUS
FROM PO_HEADERS_DRAFT_ALL PH
WHERE PH.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND PH.DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2 ) PO_HEADER_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( TOTAL_AMOUNT
, ACCEPTANCE_REQUIRED_FLAG
, ACCEPTANCE_DUE_DATE
, CLM_CONTRACT_OFFICER
, USER_DOCUMENT_STATUS
, VENDOR_SITE_ID
, VENDOR_CONTACT_ID
, SUPPLIER_NOTIF_METHOD
, FAX
, EMAIL_ADDRESS
, CLM_EXTERNAL_IDV
, CLM_VENDOR_OFFER_NUMBER
, CLM_EFFECTIVE_DATE
, AGENT_ID
, CLM_AWARD_ADMINISTRATOR
, COMMENTS
, CONFIRMING_ORDER_FLAG
, TERMS_ID
, CLM_NO_SIGNED_COPIES_TO_RETURN
, SHIP_TO_LOCATION_ID
, SHIP_VIA_LOOKUP_CODE
, FREIGHT_TERMS_LOOKUP_CODE
, FOB_LOOKUP_CODE
, SHIPPING_CONTROL
, NOTE_TO_VENDOR
, NOTE_TO_RECEIVER
, BLANKET_TOTAL_AMOUNT
, PRICE_UPDATE_TOLERANCE
, START_DATE
, END_DATE
, AMOUNT_LIMIT
, CLM_AMOUNT_RELEASED
, CLM_MAX_ORDER_AMOUNT
, CLM_MIN_ORDER_AMOUNT
, CLM_MIN_GUARANTEE_AWARD_AMT
, CLM_MIN_GUAR_AWARD_AMT_PERCENT
, CHANGE_STATUS ) )