PO Award Data template
Description
Categories: BI Publisher
Application: Purchasing
Source:
Short Name: PO_STD_PO
DB package: PO_DataTemplate_Pkg
Source:
Short Name: PO_STD_PO
DB package: PO_DataTemplate_Pkg
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
SELECT PHA.TYPE_LOOKUP_CODE tlc, PHA.SEGMENT1, PHA.REVISION_NUM 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, PHA.CONFIRMING_ORDER_FLAG, decode(:is_doc_mod, 'Y', DECODE(NVL(PHA.Acceptance_required_flag, 'N'), 'N', 'N', 'Y'), DECODE(nvl(PHA.ACCEPTANCE_REQUIRED_FLAG,'N'), 'N', decode(PO_DATATEMPLATE_PKG.get_bilateral_indicator(&pt_headerId,:draftId,pha.AUTHORIZATION_STATUS, 'N') , 'N', 'N', 'Y'), 'Y')) acc_reqd_flag , PHA.ACCEPTANCE_DUE_DATE, FCC.CURRENCY_CODE, FCC.NAME CURRENCY_NAME, NVL(OFC.FREIGHT_CODE_TL, PHA.SHIP_VIA_LOOKUP_CODE) SHIP_VIA, &p_fob_value_dest FP_FOB_LOOKUP_CODE_D, &p_fob_value_origin FP_FOB_LOOKUP_CODE_O, 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_DATATEMPLATE_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, po_datatemplate_pkg.get_inv_office_details(pha.uda_template_id,&pt_headerId, :draftId) FP_SF1449_INV_OFC, 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, 'INCOMPLETE') AUTHORIZATION_STATUS, PHA.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_MEANING, 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_AWARD_TYPE,PHA.CLM_CONTRACT_OFFICER cco, DECODE (NVL(PHA.CLM_DOCUMENT_FORMAT, 'N'), 'COM' , fnd_message.get_string('PO', 'PO_INV_OFC_SEC_COMAWD'), 'CSI', fnd_message.get_string('PO', 'PO_INV_OFC_SEC_CSI'), 'UCF', fnd_message.get_string('PO', 'PO_INV_OFC_SEC_UCF'), '') FP_INV_OFC_SECTION, (nvl2( PHA.TERMS_ID , DECODE((SELECT 1 FROM ap_terms_lines where term_id = PHA.TERMS_ID group by term_id, due_days having count(due_days) = 1), 1, (select ( nvl2(discount_days, discount_days || ' ' || FND_MESSAGE.GET_STRING('PO', 'PO_TOLERANCE_DAYS') || ' - ' || discount_percent || '%; ', '') || nvl2(discount_days_2, discount_days_2 || ' ' || FND_MESSAGE.GET_STRING('PO', 'PO_TOLERANCE_DAYS') || ' - ' || discount_percent_2 || '%; ', '') || nvl2(discount_days_3, discount_days_3 || ' ' || FND_MESSAGE.GET_STRING('PO', 'PO_TOLERANCE_DAYS') || ' - ' || discount_percent_3 || '%; ', '') || FND_MESSAGE.GET_STRING('PO', 'PO_NET_DAYS_MSG') || ' - ' || due_days)val from ap_terms_lines ATL where ATL.term_id =PHA.TERMS_ID), fnd_message.get_string('PO', 'PO_SEE_SCHEDULE')), '')) FP_PAYMENT_TERM, nvl(pha.clm_external_idv, (SELECT clm_document_number from po_headers_all pha2 where pha.clm_source_document_id = pha2.po_header_id)) CLM_ORDER_NUMBER, SUBSTR (PHA.CLM_DOCUMENT_NUMBER, -4, 4) SPIIN, PHA.CLM_SOURCE_DOCUMENT_ID, NVL(flvs.meaning, pha.authorization_status) authorization_status_meaning, (select sum(nvl(pd.funded_value,0)) from po_distributions_merge_v pd where pd.po_header_id = &pt_headerId and pd.draft_id = :draftId) AMOUNT_FUNDED, PO_CORE_S2.GET_BASE_CURRENCY(PHA.ORG_ID) BASE_CURRENCY_CODE, DECODE(nvl(PHA.AUTHORIZATION_STATUS,'INCOMPLETE'), 'INCOMPLETE', fnd_message.get_string('PO', 'PO_FO_DRAFT'), 'IN PROCESS', decode((SELECT nvl(can_approver_modify_doc_flag, 'N') from po_document_types_all where document_type_code = 'PO' and document_subtype = pha.type_lookup_code and org_id = pha.org_id), 'Y' ,fnd_message.get_string('PO', 'PO_FO_DRAFT'), NULL), 'REQUIRES REAPPROVAL', fnd_message.get_string('PO', 'PO_FO_DRAFT'), 'REJECTED', fnd_message.get_string('PO', 'PO_FO_DRAFT'), NULL) DRAFT_TEXT, PHA.CLM_ISSUING_OFFICE, PHA.CLM_COTR_OFFICE, PHA.CLM_COTR_CONTACT, PHA.CLM_PRIORITY_CODE, PHA.CLM_STANDARD_FORM, PHA.CLM_DOCUMENT_FORMAT, 0 AS TOTAL_NO_OF_PAGES, DECODE(PHA.CLM_AWARD_TYPE, 'DELIVERY_ORD' , 'D' , 'BPA_CALL' , 'D' , 'PURCH_ORD' , 'P', 'N') FP_AWARD_TYPE_B, pha.ame_transaction_type, pha.clm_mod_issuing_office, NVL2(PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, 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, :draftId, NULL, NULL, NULL, NULL, 'AWD_CTGR', NULL, 'NO_COMP_RSN', 'INTERNAL_VALUE' ), '10_2304(C)(1)' , '10USC2304c' , '10_2304(C)(2)', '10USC2304c', '10_2304(C)(3)' , '10USC2304c', '10_2304(C)(4)' , '10USC2304c', '10_2304(C)(5)' , '10USC2304c', '10_2304(C)(6)' , '10USC2304c', '10_2304(C)(7)' , '10USC2304c', '41_253(C)(1)', '41USC253c', '41_253(C)(2)', '41USC253c', '41_253(C)(3)', '41USC253c', '41_253(C)(4)', '41USC253c', '41_253(C)(5)', '41USC253c', '41_253(C)(6)', '41USC253c', '41_253(C)(7)', '41USC253c') H_NO_COMP_RSN, DECODE (PO_UDA_PUB.get_single_attr_value(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'AWD_CTGR', NULL, 'NO_COMP_RSN', 'INTERNAL_VALUE' ), '10_2304(C)(1)' , '1' , '10_2304(C)(2)', '2', '10_2304(C)(3)' , '3', '10_2304(C)(4)' , '4', '10_2304(C)(5)' , '5', '10_2304(C)(6)' , '6', '10_2304(C)(7)' , '7') H_NO_COMP_RSN_10_PRGRPH, DECODE (PO_UDA_PUB.get_single_attr_value(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'AWD_CTGR', NULL, 'NO_COMP_RSN', 'INTERNAL_VALUE' ), '41_253(C)(1)', '1', '41_253(C)(2)', '2', '41_253(C)(3)', '3', '41_253(C)(4)', '4', '41_253(C)(5)', '5', '41_253(C)(6)', '6', '41_253(C)(7)', '7') H_NO_COMP_RSN_41_PRGRPH, PO_UCA.get_header_amount_ordered(&pt_headerId, :draftId,pha.AUTHORIZATION_STATUS) HEADER_TOTAL_AMOUNT, PO_DT_CUSTOM_PKG.get_clause_number(&pt_headerId) SF1449_FAR_CLAUSE, PO_DT_CUSTOM_PKG.is_addenda_attached(&pt_headerId) SF1449_ADDENDA_ATTACH, PO_DT_CUSTOM_PKG.is_doc_award_or_notice(&pt_headerId) SF1442_AWD_OR_NOTICE, PO_DT_CUSTOM_PKG.is_guarantee_reqd(&pt_headerId) SF1442_GUA_REQD, PO_DT_CUSTOM_PKG.get_calendar_days(&pt_headerId) SF14442_CAL_DAYS, FND_MESSAGE.GET_STRING('PO', 'PO_FO_DRAFT') AS DRAFT_TAG, (PO_DATATEMPLATE_PKG.GET_UDA_HEADER_DRAFTS_XML(&pt_headerId)).GETCLOBVAL() UDA_ATTR, PO_DATATEMPLATE_PKG.get_drafts_addressxml(&pt_headerId) addresses, PO_DATATEMPLATE_PKG.get_drafts_contactsxml(&pt_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 backing_pr) from (SELECT DISTINCT h.segment1 backing_pr 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 = &pt_headerId and pod.draft_id = :draftId 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 UNION SELECT h.segment1 backing_pr FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l WHERE pol.po_header_id = &pt_headerId and pol.draft_id = :draftId and pol.line_location_id = l.line_location_id and l.requisition_header_id = h.requisition_header_id and Nvl(fund_source_not_known,'N') = 'Y') ) , 0, NULL , 1 , (SELECT DISTINCT backing_pr from (SELECT DISTINCT h.segment1 backing_pr 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 = &pt_headerId and pod.draft_id = :draftId 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 union SELECT h.segment1 backing_pr FROM po_line_locations_merge_v pol, po_requisition_headers_all h, po_requisition_lines_all l WHERE pol.po_header_id = &pt_headerId and pol.draft_id = :draftId and pol.line_location_id = l.line_location_id and l.requisition_header_id = h.requisition_header_id and Nvl(fund_source_not_known,'N') = 'Y') ) , fnd_message.get_string('PO', 'PO_SEE_SCHEDULE')) REF_PR_NUM_T, fnd_profile.value('OKC_AMENDMENT_SPECIFIC_SECTION') amend_clause_section, (CASE WHEN :is_doc_mod = 'Y' OR PHA.REVISION_NUM > 0 THEN fnd_message.get_string('PO', 'PO_CONFORM_UNOFF_COPY') ELSE NULL END) conformed_copy_value, NVL(PHA.SUPPLIER_NOTIF_METHOD, 'NONE') SUPPLIER_NOTIF_MTHD, DECODE (PHA.SUPPLIER_NOTIF_METHOD, 'EMAIL', PHA.EMAIL_ADDRESS, NULL) SUPPLIER_NOTIF_METHOD_EMAIL, DECODE (PHA.SUPPLIER_NOTIF_METHOD, 'FAX', PHA.FAX, NULL) SUPPLIER_NOTIF_METHOD_FAX, decode(:is_doc_mod, 'Y', DECODE(nvl(PHA.ACCEPTANCE_REQUIRED_FLAG,'N'), 'N', '', PHA.CLM_NO_SIGNED_COPIES_TO_RETURN), DECODE(nvl(PHA.ACCEPTANCE_REQUIRED_FLAG,'N'), 'N', decode(PO_DATATEMPLATE_PKG.get_bilateral_indicator(&pt_headerId,:draftId,pha.AUTHORIZATION_STATUS, 'N') , 'N', '', PHA.CLM_NO_SIGNED_COPIES_TO_RETURN), PHA.CLM_NO_SIGNED_COPIES_TO_RETURN)) FP_NEG_AGR_NO_OF_COPY, decode(:is_doc_mod, 'Y', DECODE(NVL(PHA.Acceptance_required_flag, 'N'), 'N', PHA.CLM_VENDOR_OFFER_NUMBER, ''), DECODE(nvl(PHA.ACCEPTANCE_REQUIRED_FLAG,'N'), 'N', decode(PO_DATATEMPLATE_PKG.get_bilateral_indicator(&pt_headerId,:draftId,pha.AUTHORIZATION_STATUS, 'N') , 'N', PHA.CLM_VENDOR_OFFER_NUMBER, ''), '')) FP_AWD_VENDOR_OFFER_NUM, decode( PO_UDA_PUB.get_single_attr_value(pha.uda_template_id, NULL, &pt_headerId, :draftId , NULL, NULL, NULL, NULL, 'SUPPLIER_DTLS', NULL, 'REMIT_TO_ADDRS', 'INTERNAL_VALUE') , pha.vendor_site_id, 'Y', 'N') fp_is_remit_addr_site_same, &p_fp_of347_small FP_OF347_SMALL, &p_fp_of347_other FP_OF347_OTHER, &p_fp_of347_disadv FP_OF347_DISADV, &p_fp_of347_women FP_OF347_WOMEN, &p_fp_of347_hubzone FP_OF347_HUBZ, &p_fp_of347_SDVO FP_OF347_SDVO, &p_fp_of347_WOSB FP_OF347_WOSB, &p_fp_of347_EDWOSB FP_OF347_EDWOSB, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1447_Informa', NULL, 'TYPE_PURCHASE', 'INTERNAL_VALUE')), 'SERVICES', 'Y', 'BOTH', 'Y', 'N') FP_SF1447_purchase_services, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1447_Informa', NULL, 'TYPE_PURCHASE', 'INTERNAL_VALUE')), 'SUPPLIES', 'Y', 'BOTH', 'Y', 'N') FP_SF1447_purchase_supplies, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'OF347_Informa', NULL, 'TYPE_OF_ORDER', 'INTERNAL_VALUE')), 'DELIVERY', 'Y', 'BOTH', 'Y', 'N') FP_OF347_delivery_order, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'OF347_Informa', NULL, 'TYPE_OF_ORDER', 'INTERNAL_VALUE')), 'PURCHASE', 'Y', 'BOTH', 'Y', 'N') FP_OF347_purchase_order, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1449_Informa', NULL, 'AWD_OF_CONTR', 'INTERNAL_VALUE')), 'Y', (PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'Offer_Informa', NULL, 'SUPP_OFFR_NUM', 'INTERNAL_VALUE')) , NULL) FP_SF1449_supp_offr_num, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1449_Informa', NULL, 'AWD_OF_CONTR', 'INTERNAL_VALUE')), 'Y', (PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'Offer_Informa', NULL, 'SUPP_OFFR_DATE', 'INTERNAL_VALUE')) , NULL) FP_SF1449_supp_offr_date, PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1449_Informa', NULL, 'AWD_OF_CONTR', 'INTERNAL_VALUE') FP_SF1449_awd_of_contr, PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1447_Informa', NULL, 'AWD_OF_CONTR', 'INTERNAL_VALUE') FP_SF1447_awd_of_contr, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1449_Informa', NULL, 'AWD_OF_CONTR', 'INTERNAL_VALUE')), 'Y', fnd_message.get_string('PO', 'PO_SEE_SCHEDULE') , NULL) FP_SF1449_offr_items_text, DECODE((PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SF1447_Informa', NULL, 'AWD_OF_CONTR', 'INTERNAL_VALUE')), 'Y', fnd_message.get_string('PO', 'PO_SEE_SCHEDULE') , NULL) FP_SF1447_offr_items_text, (select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = &pt_headerId and sec.document_type = po_conterms_utl_grp.get_po_contract_doctype(PHA.type_lookup_code) and sec.heading = 'Section C' and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_c_clauses, (select 'Y' from po_lines_all_ext_b ple, po_lines_merge_v pl, ego_attr_groups_v eag where pl.po_header_id = &pt_headerId and pl.draft_id = :draftId and ple.po_line_id = pl.po_line_id and ple.draft_id = :draftId and eag.attr_group_name = 'INSPECT_INFO' and eag.attr_group_type = 'PO_LINE_EXT_ATTRS' and eag.attr_group_id = ple.attr_group_id and rownum = 1) has_inspection_info, (select 'Y' from po_line_locations_all_ext_b plle, PO_LINE_LOCATIONS_MERGE_V pll, ego_attr_groups_v eag where pll.po_header_id = &pt_headerId and pll.draft_id = :draftId and plle.line_location_id = pll.line_location_id and plle.draft_id = :draftId and eag.attr_group_name = 'SHIP_INFO' and eag.attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS' and eag.attr_group_id = plle.attr_group_id and rownum = 1) has_delivery_info, DECODE((select 'Y' from po_distributions_merge_v where po_header_id = &pt_headerId and draft_id = :draftId and rownum = 1), 'Y', 'Y', (select 'Y' from po_headers_all_ext_b phe, ego_attr_groups_v eag where phe.po_header_id = &pt_headerId and phe.draft_id = :draftId and eag.attr_group_name = 'addresses' and eag.attr_group_type = 'PO_HEADER_EXT_ATTRS' and eag.attr_group_id = phe.attr_group_id and rownum = 1)) has_contract_admin_data, (select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = &pt_headerId and sec.document_type = po_conterms_utl_grp.get_po_contract_doctype(PHA.type_lookup_code) and sec.heading = 'Section H' and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_h_clauses, (select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = &pt_headerId and sec.document_type = po_conterms_utl_grp.get_po_contract_doctype(PHA.type_lookup_code) and sec.heading = 'Section I' and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_section_i_clauses, (select 'Y' from okc_sections_b sec, okc_k_articles_b art where sec.document_id = &pt_headerId and sec.document_type = po_conterms_utl_grp.get_po_contract_doctype(PHA.type_lookup_code) and sec.heading = 'Packaging and Marking' and art.document_id = sec.document_id and art.document_type = sec.document_type and art.scn_id = sec.id and rownum = 1) has_pack_and_marking_clauses, (PO_UDA_PUB.get_single_attr_value(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, :draftId, NULL, NULL, NULL, NULL, 'SET_ASIDE_INFO', NULL, 'NAICS', 'INTERNAL_VALUE' )) fp_naics 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.TYPE_LOOKUP_CODE = 'STANDARD' AND PHA.PO_HEADER_ID =&pt_headerId AND PHA.draft_id =:draftId 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 flvs.lookup_type = 'AUTHORIZATION STATUS' AND flvs.lookup_code = NVL(pha.authorization_status, 'INCOMPLETE') AND flvs.language(+) = userenv('lang') 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 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 |
