[Home] [Help]
26: g_customer_po_line_num VARCHAR2(50);
27: g_customer_po_shipment_num VARCHAR2(50);
28: g_document_id NUMBER;
29: g_revision_num NUMBER;
30: g_vendor_id PO_HEADERS_ALL.vendor_id%type;
31: g_cover_message VARCHAR2(2001);
32: g_amendment_message VARCHAR2(2001);
33: g_test_flag VARCHAR2(1);
34: g_release_header_id PO_HEADERS_ALL.po_header_id%type;
30: g_vendor_id PO_HEADERS_ALL.vendor_id%type;
31: g_cover_message VARCHAR2(2001);
32: g_amendment_message VARCHAR2(2001);
33: g_test_flag VARCHAR2(1);
34: g_release_header_id PO_HEADERS_ALL.po_header_id%type;
35: g_location_id number;
36: g_address_line1 HR_LOCATIONS.ADDRESS_LINE_1%type := null;
37: g_address_line2 HR_LOCATIONS.ADDRESS_LINE_2%type := null;
38: g_address_line3 HR_LOCATIONS.ADDRESS_LINE_3%type := null;
37: g_address_line2 HR_LOCATIONS.ADDRESS_LINE_2%type := null;
38: g_address_line3 HR_LOCATIONS.ADDRESS_LINE_3%type := null;
39: g_Territory_short_name FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%type := null;
40: g_address_info varchar2(500) := null;
41: g_org_id PO_HEADERS_ALL.ORG_ID%type := null;
42:
43: -- Global variables to hold the Operating Unit details --
44: g_ou_name HR_ORGANIZATION_UNITS_V.NAME%type := null;
45: g_ou_address_line_1 HR_ORGANIZATION_UNITS_V.ADDRESS_LINE_1 %type := null;
50: g_ou_postal_code HR_ORGANIZATION_UNITS_V.POSTAL_CODE%type := null;
51: g_ou_country HR_ORGANIZATION_UNITS_V.COUNTRY%type := null;
52: -- End of Operation Unit detail variables --
53:
54: g_header_id PO_HEADERS_ALL.PO_HEADER_ID%type := null;
55: g_quote_number PO_HEADERS_ALL.QUOTE_VENDOR_QUOTE_NUMBER%type := null;
56: g_agreement_number PO_HEADERS_ALL.SEGMENT1%type := null;
57: g_agreement_flag PO_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%type :=null;
58: g_agreementLine_number PO_LINES_ALL.LINE_NUM%type :=null;
51: g_ou_country HR_ORGANIZATION_UNITS_V.COUNTRY%type := null;
52: -- End of Operation Unit detail variables --
53:
54: g_header_id PO_HEADERS_ALL.PO_HEADER_ID%type := null;
55: g_quote_number PO_HEADERS_ALL.QUOTE_VENDOR_QUOTE_NUMBER%type := null;
56: g_agreement_number PO_HEADERS_ALL.SEGMENT1%type := null;
57: g_agreement_flag PO_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%type :=null;
58: g_agreementLine_number PO_LINES_ALL.LINE_NUM%type :=null;
59: g_line_id PO_LINES_ALL.FROM_LINE_ID%type :=null;
52: -- End of Operation Unit detail variables --
53:
54: g_header_id PO_HEADERS_ALL.PO_HEADER_ID%type := null;
55: g_quote_number PO_HEADERS_ALL.QUOTE_VENDOR_QUOTE_NUMBER%type := null;
56: g_agreement_number PO_HEADERS_ALL.SEGMENT1%type := null;
57: g_agreement_flag PO_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%type :=null;
58: g_agreementLine_number PO_LINES_ALL.LINE_NUM%type :=null;
59: g_line_id PO_LINES_ALL.FROM_LINE_ID%type :=null;
60: g_arcBuyer_fname PER_ALL_PEOPLE_F.FIRST_NAME%type :=null;
53:
54: g_header_id PO_HEADERS_ALL.PO_HEADER_ID%type := null;
55: g_quote_number PO_HEADERS_ALL.QUOTE_VENDOR_QUOTE_NUMBER%type := null;
56: g_agreement_number PO_HEADERS_ALL.SEGMENT1%type := null;
57: g_agreement_flag PO_HEADERS_ALL.GLOBAL_AGREEMENT_FLAG%type :=null;
58: g_agreementLine_number PO_LINES_ALL.LINE_NUM%type :=null;
59: g_line_id PO_LINES_ALL.FROM_LINE_ID%type :=null;
60: g_arcBuyer_fname PER_ALL_PEOPLE_F.FIRST_NAME%type :=null;
61: g_arcBuyer_lname PER_ALL_PEOPLE_F.LAST_NAME%type :=null;
60: g_arcBuyer_fname PER_ALL_PEOPLE_F.FIRST_NAME%type :=null;
61: g_arcBuyer_lname PER_ALL_PEOPLE_F.LAST_NAME%type :=null;
62: g_arcBuyer_title PER_ALL_PEOPLE_F.TITLE%type :=null;
63: g_arcAgent_id PO_HEADERS_ARCHIVE_ALL.AGENT_ID%type :=null;
64: g_header_id1 PO_HEADERS_ALL.PO_HEADER_ID%type := null;
65: g_release_id PO_RELEASES_ALL.PO_RELEASE_ID%type :=null;
66: g_timezone VARCHAR2(255) :=NULL;
67: g_vendor_address_line_2 PO_VENDOR_SITES.ADDRESS_LINE2%type := null;
68: g_vendor_address_line_3 PO_VENDOR_SITES.ADDRESS_LINE3%type := null;
67: g_vendor_address_line_2 PO_VENDOR_SITES.ADDRESS_LINE2%type := null;
68: g_vendor_address_line_3 PO_VENDOR_SITES.ADDRESS_LINE3%type := null;
69: g_vendor_country FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%type :=null;
70: g_vendor_city_state_zipInfo varchar2(500) :=null;
71: g_vendor_site_id PO_HEADERS_ALL.vendor_site_id%type :=null;
72: g_job_id PO_LINES_ALL.JOB_ID%type :=null;
73: g_job_name PER_JOBS_VL.name%type :=null;
74: g_phone HR_LOCATIONS.TELEPHONE_NUMBER_1%type :=null;
75: --Bug 4504228 START
80: g_buyer_fax HR_LOCATIONS.TELEPHONE_NUMBER_2%type :=null; --Bug5671523 Adding g_buyer_fax
81: g_fax HR_LOCATIONS.TELEPHONE_NUMBER_2%type :=null;
82: g_location_name HR_LOCATIONS.LOCATION_CODE%type :=null;
83: g_documentType PO_DOCUMENT_TYPES_TL.TYPE_NAME%type;
84: g_currency_code PO_HEADERS_ALL.CURRENCY_CODE%type :=null;
85: g_current_currency_code PO_HEADERS_ALL.CURRENCY_CODE%type :=null;
86: g_format_mask varchar2(100) :=null;
87: g_buyer_org HR_ALL_ORGANIZATION_UNITS.NAME%type := NULL;
88: g_address_line4 HZ_LOCATIONS.ADDRESS4%TYPE := NULL; -- bug: 3463617
81: g_fax HR_LOCATIONS.TELEPHONE_NUMBER_2%type :=null;
82: g_location_name HR_LOCATIONS.LOCATION_CODE%type :=null;
83: g_documentType PO_DOCUMENT_TYPES_TL.TYPE_NAME%type;
84: g_currency_code PO_HEADERS_ALL.CURRENCY_CODE%type :=null;
85: g_current_currency_code PO_HEADERS_ALL.CURRENCY_CODE%type :=null;
86: g_format_mask varchar2(100) :=null;
87: g_buyer_org HR_ALL_ORGANIZATION_UNITS.NAME%type := NULL;
88: g_address_line4 HZ_LOCATIONS.ADDRESS4%TYPE := NULL; -- bug: 3463617
89: g_vendor_address_line_4 HZ_LOCATIONS.ADDRESS4%TYPE := NULL; -- bug: 3463617
102: g_legal_entity_town_or_city HR_LOCATIONS.TOWN_OR_CITY%type := null;
103: g_legal_entity_state HR_LOCATIONS.REGION_1%type := null;
104: g_legal_entity_postal_code HR_LOCATIONS.POSTAL_CODE%type := null;
105: g_legal_entity_country FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%type := null;
106: g_legal_entity_org_id PO_HEADERS_ALL.ORG_ID%type := null;
107:
108: -- End of Legal Entity details ----
109:
110: g_dist_shipto_count number := NULL ; -- Variable which holds count of distinct shipment ship to ids
110: g_dist_shipto_count number := NULL ; -- Variable which holds count of distinct shipment ship to ids
111: g_line_org_amount number := NULL;
112:
113: /*Bug#35833910 the variable determines whether the po has Terms and Conditions */
114: g_with_terms po_headers_all.conterms_exist_flag%type;
115:
116: --
117: g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
118: g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
180: l_revision_num number;
181: l_request_id number;
182: l_authorization_status varchar2(25);
183: x_progress varchar2(100);
184: l_with_terms PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
185:
186:
187: BEGIN
188:
463:
464: l_language_code fnd_languages.language_code%type;
465: l_document_id number;
466: l_revision_num number;
467: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
468: l_document_subtype po_headers_all.type_lookup_code%TYPE;
469: l_document_type po_headers_all.type_lookup_code%TYPE;
470: l_count number;
471: l_filename fnd_lobs.file_name%type;
464: l_language_code fnd_languages.language_code%type;
465: l_document_id number;
466: l_revision_num number;
467: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
468: l_document_subtype po_headers_all.type_lookup_code%TYPE;
469: l_document_type po_headers_all.type_lookup_code%TYPE;
470: l_count number;
471: l_filename fnd_lobs.file_name%type;
472: l_orgid number;
465: l_document_id number;
466: l_revision_num number;
467: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
468: l_document_subtype po_headers_all.type_lookup_code%TYPE;
469: l_document_type po_headers_all.type_lookup_code%TYPE;
470: l_count number;
471: l_filename fnd_lobs.file_name%type;
472: l_orgid number;
473: x_progress varchar2(100);
470: l_count number;
471: l_filename fnd_lobs.file_name%type;
472: l_orgid number;
473: x_progress varchar2(100);
474: l_with_terms PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
475: l_terms varchar2(10);
476:
477: Begin
478: x_progress := 'PO_COMMUNICATION_PVT.PO_PDF_EXISTS';
622: --l_legal_name hr_all_organization_units.name%TYPE;
623: --bug##3682458 replaced legal entity name with operating unit
624: l_operating_unit hr_all_organization_units.name%TYPE;
625:
626: l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
627: l_docNumber PO_HEADERS_ALL.SEGMENT1%TYPE;
628: l_doc_num_rel varchar2(30);
629: l_release_num PO_RELEASES.release_num%TYPE; -- Bug 3215186;
630: l_ga_flag varchar2(1) := null; -- Bug # 3290385
623: --bug##3682458 replaced legal entity name with operating unit
624: l_operating_unit hr_all_organization_units.name%TYPE;
625:
626: l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
627: l_docNumber PO_HEADERS_ALL.SEGMENT1%TYPE;
628: l_doc_num_rel varchar2(30);
629: l_release_num PO_RELEASES.release_num%TYPE; -- Bug 3215186;
630: l_ga_flag varchar2(1) := null; -- Bug # 3290385
631: l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE; -- Bug 3215186
736: l_document_id:=p_document_id;
737: end if;
738:
739: select segment1,global_agreement_flag into l_docNumber,l_ga_flag
740: from po_headers_all
741: where po_header_id = l_document_id;
742:
743: wf_engine.SetItemAttrText ( itemtype => l_itemtype,
744: itemkey => l_itemkey,
993:
994: SELECT pv.language
995: INTO l_supp_lang
996: FROM po_vendor_sites_all pv,
997: po_headers_all ph
998: WHERE ph.po_header_id = l_header_id
999: AND ph.vendor_site_id = pv.vendor_site_id;
1000:
1001:
1117: l_document_subtype po_headers.type_lookup_code%TYPE;
1118: l_document_type po_headers.type_lookup_code%TYPE;
1119: l_revision_num number;
1120: l_request_id number;
1121: l_conterm_exists PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
1122: l_authorization_status varchar2(25);
1123: l_progress varchar2(200);
1124: --l_old_request_id number; --Bug 7299381
1125: l_withterms varchar2(1);
1147: fnd_attachment_functions af,
1148: fnd_lobs fl
1149: where ((ad.pk1_value=to_char(l_po_header_id) and ad.entity_name='PO_HEADERS')
1150: OR
1151: (ad.pk1_value=to_char((select vendor_id from po_headers_all
1152: where po_header_id=l_po_header_id)) and ad.entity_name='PO_VENDORS')
1153: OR
1154: (ad.pk1_value in (select po_line_id from po_lines_all
1155: where po_header_id=l_po_header_id
1201: OR
1202: (ad.pk1_value=to_char(l_po_release_id) and ad.entity_name='PO_RELEASES')
1203: OR
1204: (ad.pk1_value=to_char((select pha.vendor_id
1205: from po_headers_all pha,po_releases_all pra
1206: where pra.po_release_id=l_po_release_id
1207: and pha.po_header_id=pra.po_header_id
1208: )) and ad.entity_name='PO_VENDORS')
1209: OR
1389: OR
1390: (ad.pk1_value=to_char(l_document_id) and ad.entity_name='PO_RELEASES')
1391: OR
1392: (ad.pk1_value=(select pha.vendor_id
1393: from po_headers_all pha,po_releases_all pra
1394: where pra.po_release_id=l_document_id
1395: and pha.po_header_id=pra.po_header_id
1396: ) and ad.entity_name='PO_VENDORS')
1397: OR
1447: fnd_attachment_functions af,
1448: fnd_lobs fl
1449: where ((ad.pk1_value=to_char(l_document_id) and ad.entity_name='PO_HEADERS')
1450: OR
1451: (ad.pk1_value=to_char((select vendor_id from po_headers_all
1452: where po_header_id=l_document_id)) and ad.entity_name='PO_VENDORS')
1453: OR
1454: (ad.pk1_value in (select po_line_id from po_lines_all
1455: where po_header_id=l_document_id
1701: WHERE po_release_id = l_document_id;
1702: END IF;
1703:
1704: SELECT pv.language into l_supp_lang
1705: FROM po_vendor_sites_all pv,po_headers_all ph
1706: WHERE
1707: ph.po_header_id = l_header_id and ph.vendor_site_id = pv.vendor_site_id;
1708:
1709:
2244: p_email_address in varchar2,
2245: p_request_id out nocopy number)
2246: IS
2247:
2248: l_conterm_exists_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
2249: l_supp_lang fnd_languages.nls_language%type;
2250: l_territory fnd_languages.nls_territory%type;
2251: l_revision_num number;
2252: l_set_lang boolean;
2318: l_progress := '010';
2319: begin
2320: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2321: l_entity_name := 'PO_HEAD';
2322: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2323: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2324: else
2325: l_entity_name := 'PO_REL';
2326: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph,po_releases_all pr
2322: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2323: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2324: else
2325: l_entity_name := 'PO_REL';
2326: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph,po_releases_all pr
2327: where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
2328: ph.vendor_site_id = pvs.vendor_site_id ;
2329: end if;
2330: IF g_debug_stmt THEN
2348: end if;
2349:
2350: l_buyer_language_code := userenv('LANG');
2351: begin
2352: select NVL(conterms_exist_flag, 'N') into l_conterm_exists_flag from po_headers_all
2353: where
2354: po_header_id = p_document_id and revision_num = p_revision_number;
2355:
2356: exception
2718: end if;
2719:
2720: l_progress := '200';
2721: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2722: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2723: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2724: else
2725: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph,po_releases_all pr
2726: where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
2721: if p_document_type in ('STANDARD','BLANKET','CONTRACT') then
2722: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2723: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2724: else
2725: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph,po_releases_all pr
2726: where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
2727: ph.vendor_site_id = pvs.vendor_site_id ;
2728: end if;
2729:
2853:
2854: --
2855: l_file_name fnd_lobs.file_name%type;
2856: l_file_content_type fnd_lobs.file_content_type%type;
2857: l_org_id po_headers_all.org_id%type;
2858: l_api_name CONSTANT VARCHAR2(25):= 'Store_Blob';
2859: l_progress VARCHAR2(3);
2860:
2861: Begin
3383: Begin
3384:
3385: if l_document_type in ('PO','PA') then
3386: select fl.language_code into l_language
3387: from po_vendor_sites_all pvs,po_headers_all ph,fnd_languages fl
3388: where ph.vendor_site_id = pvs.vendor_site_id
3389: and ph.po_header_id = l_document_id
3390: and pvs.language = fl.nls_language;
3391: elsif l_document_type = 'RELEASE' then
3389: and ph.po_header_id = l_document_id
3390: and pvs.language = fl.nls_language;
3391: elsif l_document_type = 'RELEASE' then
3392: select fl.language_code into l_language
3393: from po_vendor_sites_all pvs , po_headers_all ph,
3394: po_releases_all pr, fnd_languages fl
3395: where ph.po_header_id = pr.po_header_id
3396: and pr.po_release_id = l_document_id
3397: and ph.vendor_site_id = pvs.vendor_site_id
3517: l_language fnd_languages.language_code%type;
3518: l_withTerms varchar2(1);
3519:
3520: l_itemtype po_document_types.wf_approval_itemtype%type;
3521: l_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
3522: l_message FND_NEW_MESSAGES.message_text%TYPE;
3523:
3524: l_okc_doc_length number; -- Bug 4173198
3525:
3677: content_type in varchar2,
3678: document in out nocopy blob,
3679: document_type in out nocopy varchar2) IS
3680: l_filename fnd_lobs.file_name%type;
3681: l_document_id po_headers_all.po_header_id%type;
3682: l_document_number po_headers_all.segment1%type;
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%type;
3685: l_revision_number po_headers_all.revision_num%type;
3678: document in out nocopy blob,
3679: document_type in out nocopy varchar2) IS
3680: l_filename fnd_lobs.file_name%type;
3681: l_document_id po_headers_all.po_header_id%type;
3682: l_document_number po_headers_all.segment1%type;
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%type;
3685: l_revision_number po_headers_all.revision_num%type;
3686: l_language fnd_languages.language_code%type;
3679: document_type in out nocopy varchar2) IS
3680: l_filename fnd_lobs.file_name%type;
3681: l_document_id po_headers_all.po_header_id%type;
3682: l_document_number po_headers_all.segment1%type;
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%type;
3685: l_revision_number po_headers_all.revision_num%type;
3686: l_language fnd_languages.language_code%type;
3687: l_entity_name fnd_attached_documents.entity_name%type;
3680: l_filename fnd_lobs.file_name%type;
3681: l_document_id po_headers_all.po_header_id%type;
3682: l_document_number po_headers_all.segment1%type;
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%type;
3685: l_revision_number po_headers_all.revision_num%type;
3686: l_language fnd_languages.language_code%type;
3687: l_entity_name fnd_attached_documents.entity_name%type;
3688: l_itemtype wf_items.item_type%type;
3681: l_document_id po_headers_all.po_header_id%type;
3682: l_document_number po_headers_all.segment1%type;
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%type;
3685: l_revision_number po_headers_all.revision_num%type;
3686: l_language fnd_languages.language_code%type;
3687: l_entity_name fnd_attached_documents.entity_name%type;
3688: l_itemtype wf_items.item_type%type;
3689: l_itemkey wf_items.item_key%type;
3982: OR
3983: (ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_RELEASES')
3984: OR
3985: (ad.pk1_value=to_char((select pha.vendor_id
3986: from po_headers_all pha,po_releases_all pra
3987: where pra.po_release_id=p_document_id
3988: and pha.po_header_id=pra.po_header_id
3989: )) and ad.entity_name='PO_VENDORS')
3990: OR
4030: fnd_attachment_functions af,
4031: fnd_lobs fl
4032: where ((ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_HEADERS')
4033: OR
4034: (ad.pk1_value=to_char((select vendor_id from po_headers_all
4035: where po_header_id=p_document_id)) and ad.entity_name='PO_VENDORS')
4036: OR
4037: (ad.pk1_value in (select po_line_id from po_lines_all
4038: where po_header_id=p_document_id
4112: l_line_Attachments clob;
4113: l_shipmentAttachmentQuery varchar2(1200); --Bug5213932 increase length
4114: l_disAttachments clob;
4115: l_time varchar2(50);
4116: l_vendor_id PO_HEADERS_ALL.vendor_id%type;
4117: l_release_header_id PO_HEADERS_ALL.po_header_id%type;
4118: l_supp_org PO_VENDORS.VENDOR_NAME%type;
4119: l_po_number PO_HEADERS.SEGMENT1%type;
4120: l_message varchar2(2001);
4113: l_shipmentAttachmentQuery varchar2(1200); --Bug5213932 increase length
4114: l_disAttachments clob;
4115: l_time varchar2(50);
4116: l_vendor_id PO_HEADERS_ALL.vendor_id%type;
4117: l_release_header_id PO_HEADERS_ALL.po_header_id%type;
4118: l_supp_org PO_VENDORS.VENDOR_NAME%type;
4119: l_po_number PO_HEADERS.SEGMENT1%type;
4120: l_message varchar2(2001);
4121: l_ammendment_message varchar2(2001);
4222:
4223: /*Bug5983107 Commenting out the below sql and writing the new sql below without reference to hr_all_organization_units*/
4224: /* SELECT hle.name, vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
4225: INTO po_communication_pvt.g_buyer_org, l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
4226: FROM hr_all_organization_units hle, po_vendors vn, po_headers_all ph
4227: WHERE to_char(hle.organization_id) = (select org_information2 from hr_organization_information where
4228: org_information_context = 'Operating Unit Information' and organization_id = ph.org_id) AND vn.vendor_id = ph.vendor_id
4229: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num; */
4230:
4229: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num; */
4230:
4231: SELECT vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
4232: INTO l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
4233: FROM po_vendors vn, po_headers_all ph
4234: WHERE vn.vendor_id = ph.vendor_id
4235: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num;
4236:
4237:
4260: END IF;
4261:
4262: /*Bug5983107 Use the below API to get the legal entity name */
4263: BEGIN
4264: select org_id into l_org_id from po_headers_all where po_header_id= p_document_id ;
4265: l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_org_id);
4266:
4267: XLE_UTILITIES_GRP.Get_LegalEntity_Info(
4268: x_return_status,
4294: -- select the header id into g_release_header_id global variable for a given release id.
4295: SELECT po_header_id INTO PO_COMMUNICATION_PVT.g_release_header_id FROM po_releases_all WHERE po_release_id = p_document_id;
4296:
4297: SELECT ph.vendor_id, ph.currency_code INTO l_vendor_id, g_current_currency_code
4298: FROM po_vendors vn, po_headers_all ph
4299: WHERE vn.vendor_id = ph.vendor_id
4300: AND ph.po_header_id = PO_COMMUNICATION_PVT.g_release_header_id ;
4301:
4302: IF p_which_tables = 'MAIN' THEN
6122: Select ph.QUOTE_VENDOR_QUOTE_NUMBER, ph.SEGMENT1, ph.GLOBAL_AGREEMENT_FLAG into
6123: PO_COMMUNICATION_PVT.g_quote_number, PO_COMMUNICATION_PVT.g_agreement_number,
6124: PO_COMMUNICATION_PVT.g_agreement_flag
6125: FROM
6126: po_headers_all ph
6127: WHERE
6128: ph.PO_HEADER_ID = p_header_id;
6129:
6130:
6410: p_revision_num number,
6411: p_language_code varchar2,
6412: p_extension varchar2) RETURN varchar2 IS
6413:
6414: l_po_number po_headers_all.segment1%type;
6415: l_language_code fnd_languages.language_code%type;
6416: l_api_name CONSTANT VARCHAR2(25):= 'PDFFileName';
6417: l_file_name fnd_lobs.file_name%type;
6418: l_progress VARCHAR2(3);
6443:
6444: -- Query for getting the PO number i.e segment1.
6445: IF p_document_type in ('PO','PA') THEN
6446: SELECT ph.segment1 into l_po_number
6447: FROM po_headers_all ph
6448: WHERE po_header_id = p_document_id ;
6449: ELSE
6450: SELECT ph.segment1 into l_po_number
6451: FROM po_headers_all ph,po_releases_all pr
6447: FROM po_headers_all ph
6448: WHERE po_header_id = p_document_id ;
6449: ELSE
6450: SELECT ph.segment1 into l_po_number
6451: FROM po_headers_all ph,po_releases_all pr
6452: WHERE ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id ;
6453: END IF;
6454:
6455: EXCEPTION
6903: SELECT NVL(poh.pending_signature_flag, 'N')
6904: , hou.name
6905: INTO x_pendingSignatureFlag
6906: , PO_COMMUNICATION_PVT.g_ou_name
6907: FROM po_headers_all poh
6908: , hr_all_organization_units hou
6909: WHERE poh.po_header_id = p_documentID
6910: AND hou.organization_id = poh.org_id;
6911:
6962:
6963: d_progress NUMBER;
6964: d_module VARCHAR2(70) := 'po.plsql.PO_COMMUNICATION_PVT.setIsComplexWorkPO';
6965: l_is_complex BOOLEAN;
6966: l_style_id PO_HEADERS_ALL.style_id%TYPE;
6967:
6968:
6969: BEGIN
6970:
7078: p_document_type VARCHAR2,
7079: p_profile_option VARCHAR2) RETURN VARCHAR2
7080: IS
7081:
7082: x_item_type po_headers_all.wf_item_type%TYPE;
7083: x_item_key po_headers_all.wf_item_key%TYPE;
7084: l_profile_value fnd_profile_option_values.profile_option_value%TYPE;
7085: l_progress VARCHAR2(10);
7086:
7079: p_profile_option VARCHAR2) RETURN VARCHAR2
7080: IS
7081:
7082: x_item_type po_headers_all.wf_item_type%TYPE;
7083: x_item_key po_headers_all.wf_item_key%TYPE;
7084: l_profile_value fnd_profile_option_values.profile_option_value%TYPE;
7085: l_progress VARCHAR2(10);
7086:
7087: l_preparer_user_id NUMBER;
7110:
7111: ELSE
7112: SELECT wf_item_type, wf_item_key
7113: INTO x_item_type, x_item_key
7114: FROM po_headers_all
7115: WHERE po_header_id = p_document_id;
7116:
7117: l_progress := '002';
7118: