SELECT PHA.TYPE_LOOKUP_CODE tlc, PHA.SEGMENT1, PHA.REVISION_NUM, PHA.PRINT_COUNT, PHA.CREATION_DATE,
PHA.PRINTED_DATE, PHA.REVISED_DATE, PHA.START_DATE, PHA.END_DATE, PHA.NOTE_TO_VENDOR,
PHA.AGENT_ID DOCUMENT_BUYER_AGENT_ID,PHA.RATE,
PHA.CANCEL_FLAG, NVL(PHA.ACCEPTANCE_REQUIRED_FLAG,'N') ACCEPTANCE_REQUIRED_FLAG,
PHA.CONFIRMING_ORDER_FLAG, DECODE(PHA.Acceptance_required_flag, NULL, 'N', 'N', 'N' , 'Y') Acceptance_required_flag ,
PHA.ACCEPTANCE_DUE_DATE, FCC.CURRENCY_CODE, FCC.NAME CURRENCY_NAME, NVL(OFC.FREIGHT_CODE_TL, PHA.SHIP_VIA_LOOKUP_CODE) SHIP_VIA,
PLC1.MEANING FOB, PLC2.MEANING FREIGHT_TERMS, T.NAME PAYMENT_TERMS,
PHA.SHIP_TO_LOCATION_ID ship_location_id, PHA.BILL_TO_LOCATION_ID bill_location_id,
(SELECT HLC.LOCATION_CODE FROM HR_LOCATIONS HLC WHERE HLC.LOCATION_ID = PHA.SHIP_TO_LOCATION_ID) LOCATION_NAME,
PHA.ATTRIBUTE1, PHA.ATTRIBUTE2, PHA.ATTRIBUTE3, PHA.ATTRIBUTE4, PHA.ATTRIBUTE5, PHA.ATTRIBUTE6, PHA.ATTRIBUTE7, PHA.ATTRIBUTE8,
PHA.ATTRIBUTE9, PHA.ATTRIBUTE10, PHA.ATTRIBUTE11, PHA.ATTRIBUTE12, PHA.ATTRIBUTE13, PHA.ATTRIBUTE14, PHA.ATTRIBUTE15,
PHA.VENDOR_SITE_ID VSI, PHA.PO_HEADER_ID, DECODE(PHA.APPROVED_FLAG,'Y','Y','N') APPROVED_FLAG, PHA.VENDOR_ID VNI, PHA.CLOSED_CODE, PHA.USSGL_TRANSACTION_CODE,
PHA.GOVERNMENT_CONTEXT, PHA.REQUEST_ID, PHA.PROGRAM_APPLICATION_ID, PHA.PROGRAM_ID, PHA.PROGRAM_UPDATE_DATE,
PHA.ORG_ID ORG_T_ID, PO_XML_FPDSNG_PKG.GET_LOCATION_ID(PHA.ORG_ID) LEGAL_ENTITY_LOCATION_ID,
PHA.COMMENTS, PHA.REPLY_DATE, PHA.REPLY_METHOD_LOOKUP_CODE,
PHA.RFQ_CLOSE_DATE , PHA.QUOTE_TYPE_LOOKUP_CODE,
PHA.QUOTATION_CLASS_CODE,PHA.QUOTE_WARNING_DELAY_UNIT, PHA.QUOTE_WARNING_DELAY, PHA.QUOTE_VENDOR_QUOTE_NUMBER,
PHA.CLOSED_DATE, PHA.USER_HOLD_FLAG, PHA.APPROVAL_REQUIRED_FLAG, PHA.FIRM_STATUS_LOOKUP_CODE, PHA.FIRM_DATE,
PHA.FROZEN_FLAG, PHA.EDI_PROCESSED_FLAG, PHA.EDI_PROCESSED_STATUS, PHA.ATTRIBUTE_CATEGORY, PHA.CREATED_BY,
PHA.VENDOR_CONTACT_ID VCI, PHA.TERMS_ID, PHA.STATUS_LOOKUP_CODE, PHA.RATE_TYPE, PHA.RATE_DATE, PHA.FROM_HEADER_ID,
PHA.FROM_TYPE_LOOKUP_CODE, NVL(PHA.AUTHORIZATION_STATUS, 'N') AUTHORIZATION_STATUS, DECODE(PHA.status, 'COMPLETED', PHA.APPROVED_DATE, NULL) APPROVED_DATE, PHA.AMOUNT_LIMIT, PHA.MIN_RELEASE_AMOUNT,
PHA.NOTE_TO_AUTHORIZER, PHA.NOTE_TO_RECEIVER, PHA.VENDOR_ORDER_NUM, PHA.LAST_UPDATE_DATE, PHA.LAST_UPDATED_BY,
PHA.SUMMARY_FLAG, PHA.ENABLED_FLAG, PHA.SEGMENT2, PHA.SEGMENT3, PHA.SEGMENT4, PHA.SEGMENT5, PHA.START_DATE_ACTIVE,
PHA.END_DATE_ACTIVE, PHA.LAST_UPDATE_LOGIN, PHA.SUPPLY_AGREEMENT_FLAG, PHA.GLOBAL_ATTRIBUTE_CATEGORY,
PHA.GLOBAL_ATTRIBUTE1, PHA.GLOBAL_ATTRIBUTE2, PHA.GLOBAL_ATTRIBUTE3, PHA.GLOBAL_ATTRIBUTE4, PHA.GLOBAL_ATTRIBUTE5,
PHA.GLOBAL_ATTRIBUTE6, PHA.GLOBAL_ATTRIBUTE7, PHA.GLOBAL_ATTRIBUTE8, PHA.GLOBAL_ATTRIBUTE9, PHA.GLOBAL_ATTRIBUTE10,
PHA.GLOBAL_ATTRIBUTE11, PHA.GLOBAL_ATTRIBUTE12, PHA.GLOBAL_ATTRIBUTE13, PHA.GLOBAL_ATTRIBUTE14, PHA.GLOBAL_ATTRIBUTE15,
PHA.GLOBAL_ATTRIBUTE16, PHA.GLOBAL_ATTRIBUTE17, PHA.GLOBAL_ATTRIBUTE18, PHA.GLOBAL_ATTRIBUTE19, PHA.GLOBAL_ATTRIBUTE20,
PHA.INTERFACE_SOURCE_CODE, PHA.REFERENCE_NUM, PHA.WF_ITEM_TYPE, PHA.WF_ITEM_KEY, PHA.PCARD_ID, PHA.PRICE_UPDATE_TOLERANCE,
PHA.MRC_RATE_TYPE, PHA.MRC_RATE_DATE, PHA.MRC_RATE, PHA.PAY_ON_CODE, PHA.XML_FLAG, PHA.XML_SEND_DATE,
PHA.XML_CHANGE_SEND_DATE, PHA.GLOBAL_AGREEMENT_FLAG, PHA.CONSIGNED_CONSUMPTION_FLAG, PHA.CBC_ACCOUNTING_DATE,
PHA.CONSUME_REQ_DEMAND_FLAG, PHA.CHANGE_REQUESTED_BY, PLC3.MEANING SHIPPING_CONTROL_DESC,
PHA.SHIPPING_CONTROL, PHA.CONTERMS_EXIST_FLAG,
PHA.CONTERMS_ARTICLES_UPD_DATE, PHA.CONTERMS_DELIV_UPD_DATE, NVL(PHA.PENDING_SIGNATURE_FLAG,'N'),
PHA.CHANGE_SUMMARY, PA.LOCATION_ID b_location_id,
PHA.DOCUMENT_CREATION_METHOD, PHA.ENCUMBRANCE_REQUIRED_FLAG, PSL.DISPLAY_NAME STYLE_DISPLAY_NAME,
PHA.UDA_TEMPLATE_ID uti, PHA.UDA_TEMPLATE_DATE,
PHA.USER_DOCUMENT_STATUS, PHA.AME_APPROVAL_ID, PHA.DRAFT_ID, PHA.CLM_EFFECTIVE_DATE,PHA.CLM_AWARD_ADMINISTRATOR caa,
PHA.CLM_VENDOR_OFFER_NUMBER, PHA.CLM_NO_SIGNED_COPIES_TO_RETURN, PHA.CLM_MIN_GUARANTEE_AWARD_AMT, PHA.CLM_MIN_GUAR_AWARD_AMT_PERCENT,
PHA.CLM_MIN_ORDER_AMOUNT, PHA.CLM_MAX_ORDER_AMOUNT, PHA.CLM_AMT_SYNCED_TO_AGREEMENT, PHA.CLM_AMOUNT_RELEASED, PHA.CLM_EXTERNAL_IDV,
PHA.CLM_SUPPLIER_NAME, PHA.CLM_SUPPLIER_SITE_NAME, PHA.CLM_DOCUMENT_NUMBER, PHA.CLM_SOURCE_DOCUMENT_ID, PHA.CLM_ISSUING_OFFICE,
PHA.CLM_COTR_OFFICE, PHA.CLM_COTR_CONTACT, PHA.CLM_PRIORITY_CODE, PHA.CLM_STANDARD_FORM, PHA.CLM_DOCUMENT_FORMAT,
PHA.BLANKET_TOTAL_AMOUNT AGREED_AMOUNT,
NVL(flvs.meaning, pha.authorization_status) authorization_status_meaning,
PO_PARTIAL_FUNDING_PKG.GET_FUNDED_VALUE('PO', :headerId) AMOUNT_FUNDED,
PO_CORE_S2.GET_BASE_CURRENCY(PHA.ORG_ID) BASE_CURRENCY_CODE,
DECODE ((SELECT COUNT(NVL(FOB_LOOKUP_CODE, 'N')) FROM PO_LINE_LOCATIONS_MERGE_V WHERE PO_HEADER_ID =:headerId
AND NVL(FOB_LOOKUP_CODE, 'N') <> 'Destination'), 0 , 'N' ,'Y')
FOB_LOOKUP_CODE_T,
NVL2(PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.draft_id, NULL, NULL, NULL, NULL,
'BU_PRI_PRJ_INF', NULL, 'DPAS_RATING', 'INTERNAL_VALUE' ), 'Y', 'N') DPAS_RATED,
DECODE (PO_UDA_PUB.get_single_attr_value(pha.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID , PHA.draft_id , NULL, NULL, NULL, NULL,
'mod_Just', NULL, 'mod_auth', 'INTERNAL_VALUE' ), 'CHANGE_PURSUNT_TO' , PO_UDA_PUB.get_single_attr_value(pha.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID , PHA.draft_id , NULL, NULL, NULL, NULL,
'mod_Just', NULL, 'auth_text', 'INTERNAL_VALUE' ) ) AUTH_TEXT_CHANGE,
DECODE (PO_UDA_PUB.get_single_attr_value(pha.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID , PHA.draft_id , NULL, NULL, NULL, NULL,
'mod_Just', NULL, 'mod_auth', 'INTERNAL_VALUE' ), 'SUPPLEMENTAL_AGREE_PURSUANT' , PO_UDA_PUB.get_single_attr_value(pha.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID , PHA.draft_id , NULL, NULL, NULL, NULL,
'mod_Just', NULL, 'auth_text', 'INTERNAL_VALUE' ) ) AUTH_TEXT_SUPPLEMENTAL,
DECODE (PO_UDA_PUB.get_single_attr_value(pha.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID , PHA.draft_id , NULL, NULL, NULL, NULL,
'mod_Just', NULL, 'mod_auth', 'INTERNAL_VALUsE' ), 'OTHER_AUTHORITY' , PO_UDA_PUB.get_single_attr_value(pha.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID , PHA.draft_id , NULL, NULL, NULL, NULL,
'mod_Just', NULL, 'auth_text', 'INTERNAL_VALUE' ) ) AUTH_TEXT_OTHER,
PO_XML_FPDSNG_PKG.get_header_amount_ordered(:headerId, :p_draft_id) HEADER_TOTAL_AMOUNT,
0 AS TOTAL_NO_OF_PAGES,
DECODE(PHA.AUTHORIZATION_STATUS, 'INCOMPLETE', fnd_message.get_string('PO', 'PO_FO_DRAFT'), 'IN PROCESS',
fnd_message.get_string('PO', 'PO_FO_DRAFT'), 'REQUIRES REAPPROVAL', fnd_message.get_string('PO', 'PO_FO_DRAFT'),
NULL) DRAFT_TEXT,
(SELECT DISTINCT PLA.auction_header_id from PO_LINES_ALL PLA WHERE PLA.PO_HEADER_ID =:headerId) ahi,
FND_MESSAGE.GET_STRING('PO', 'PO_FO_DRAFT') AS DRAFT_TAG,
nvl((SELECT segment1 from po_headers_all pha1 where pha1.po_header_Id = pha.clm_source_document_id),
PHA.CLM_EXTERNAL_IDV) REFERENCE_IDV_PIID,
(PO_XML_FPDSNG_PKG.GET_UDA_HEADER_DRAFTS_XML(:headerId)).GETCLOBVAL() UDA_ATTR,
PO_XML_FPDSNG_PKG.get_drafts_addressxml(:headerId) addresses,
PO_XML_FPDSNG_PKG.get_drafts_contactsxml(:headerId) contact_details,
(SELECT NVL(PROGRESS_PAYMENT_FLAG, 'N') FROM PO_DOC_STYLE_HEADERS
WHERE STYLE_ID = PHA.STYLE_ID) IS_COMPLEX_WORK_PO,
DECODE ((SELECT count( DISTINCT h.segment1) FROM PO_DISTRIBUTIONS_MERGE_V pod,
po_req_distributions_all prd, po_requisition_headers_all h, po_requisition_lines_all l
WHERE pod.po_header_id = :headerId AND pod.req_distribution_id = prd.distribution_id
AND prd.requisition_line_id = l.requisition_line_id AND l.requisition_header_id = h.requisition_header_id) , 0, NULL ,
1 , (SELECT DISTINCT h.segment1 FROM PO_DISTRIBUTIONS_MERGE_V pod,
po_req_distributions_all prd, po_requisition_headers_all h, po_requisition_lines_all l
WHERE pod.po_header_id = :headerId AND pod.req_distribution_id = prd.distribution_id
AND prd.requisition_line_id = l.requisition_line_id AND l.requisition_header_id = h.requisition_header_id) ,
fnd_message.get_string('PO', 'PO_SEE_SCHEDULE') ) REF_PR_NUM_T,
fnd_profile.value('OKC_AMENDMENT_SPECIFIC_SECTION') amend_clause_section,
decode(:is_doc_mod , 'Y' , null, fnd_message.get_string('PO', 'PO_CONFORM_UNOFF_COPY')) conformed_copy_value,
pha.supplier_notif_method, null supp_notif_method_desc,
PHA.EMAIL_ADDRESS SUPPLIER_NOTIF_METHOD_EMAIL, PHA.FAX SUPPLIER_NOTIF_METHOD_FAX,
:cha CHG_TOT_AMT_WITH_OPT, :caeo CHG_TOT_AMT_WITHOUT_OPT,
PO_XML_FPDSNG_PKG.get_header_amount_ordered(:headerId, -1) PREV_TOT_AMT_WITH_OPT,
PO_XML_FPDSNG_PKG.get_without_opt_amount_ordered(:headerId, -1) PREV_TOT_AMT_WITHOUT_OPT,
(SELECT SUM(funded_value) FROM po_distributions_all WHERE po_header_id =:headerId) PREV_TOT_FUNDED_AMT,
((NVL((SELECT SUM(funded_value) FROM po_distributions_merge_v WHERE po_header_id =:headerId AND draft_id =:p_draft_id),0))
- (NVL((SELECT SUM(funded_value) FROM po_distributions_all WHERE po_header_id =:headerId),0))) CHG_TOT_FUNDED_AMT,
PO_XML_FPDSNG_PKG.get_issuing_off_loc_code(:headerId, :p_draft_id) ISSUING_OFFICE_LOCATION_CODE
FROM PO_HEADERS_MERGE_V PHA, FND_LOOKUP_VALUES PLC1, FND_LOOKUP_VALUES PLC2, FND_CURRENCIES_TL FCC,
AP_TERMS T, ORG_FREIGHT_TL OFC, FND_LOOKUP_VALUES PLC3, PO_DOC_STYLE_LINES_TL PSL,
PO_AGENTS PA, fnd_lookup_values flvs
WHERE PHA.PO_HEADER_ID =:headerId AND PHA.draft_id =:p_draft_id
AND PHA.TYPE_LOOKUP_CODE = 'STANDARD' AND PHA.TERMS_ID = T.TERM_ID (+) AND FCC.CURRENCY_CODE = PHA.CURRENCY_CODE
AND PLC1.LOOKUP_CODE (+) = PHA.FOB_LOOKUP_CODE AND PLC1.LOOKUP_TYPE (+) = 'FOB'
AND PLC1.LANGUAGE(+) = USERENV('LANG') AND PLC1.VIEW_APPLICATION_ID(+) = 201
AND DECODE(PLC1.LOOKUP_CODE, NULL, 1, PLC1.SECURITY_GROUP_ID) = DECODE(PLC1.LOOKUP_CODE, NULL, 1,
FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC1.LOOKUP_TYPE, PLC1.VIEW_APPLICATION_ID) )
AND PLC2.LOOKUP_CODE (+) = PHA.FREIGHT_TERMS_LOOKUP_CODE
AND flvs.lookup_type = 'AUTHORIZATION STATUS' AND flvs.lookup_code = nvl(pha.authorization_status,'INCOMPLETE') AND flvs.language(+) = userenv('lang')
AND PLC2.LOOKUP_TYPE (+)= 'FREIGHT TERMS'
AND PLC2.LANGUAGE(+)= USERENV('LANG')
AND PLC2.VIEW_APPLICATION_ID(+) = 201
AND DECODE(PLC2.LOOKUP_CODE, NULL, 1,PLC2.SECURITY_GROUP_ID) =
DECODE(PLC2.LOOKUP_CODE, NULL, 1,FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC2.LOOKUP_TYPE, PLC2.VIEW_APPLICATION_ID))
AND OFC.FREIGHT_CODE (+)= PHA.SHIP_VIA_LOOKUP_CODE
AND OFC.ORGANIZATION_ID (+)= PHA.ORG_ID
AND PA.AGENT_ID = PHA.AGENT_ID AND PLC3.LOOKUP_CODE (+) = PHA.SHIPPING_CONTROL
AND PLC3.LOOKUP_TYPE (+) = 'SHIPPING CONTROL' AND PLC3.LANGUAGE(+) = USERENV('LANG')
AND PLC3.VIEW_APPLICATION_ID(+) = 201 AND DECODE(PLC3.LOOKUP_CODE, NULL, 1, PLC3.SECURITY_GROUP_ID) =
DECODE(PLC3.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC3.LOOKUP_TYPE, PLC3.VIEW_APPLICATION_ID))
AND FCC.LANGUAGE = USERENV('LANG') AND OFC.LANGUAGE(+) = USERENV('LANG') AND PHA.STYLE_ID = PSL.STYLE_ID(+)
AND PSL.LANGUAGE(+) = USERENV('LANG') AND PSL.DOCUMENT_SUBTYPE(+) = PHA.TYPE_LOOKUP_CODE |
