[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;
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;
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;
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;
175: resultout OUT NOCOPY VARCHAR2)
176: IS
177:
178: l_document_id number;
179: l_document_subtype po_headers.type_lookup_code%TYPE;
180: l_revision_num number;
181: l_request_id number;
182: l_authorization_status varchar2(25);
183: x_progress varchar2(100);
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:
304: actid IN NUMBER,
305: funcmode IN VARCHAR2,
306: resultout OUT NOCOPY VARCHAR2) is
307: x_progress varchar2(100);
308: l_document_subtype po_headers.type_lookup_code%TYPE;
309: l_document_type po_headers.type_lookup_code%TYPE;
310:
311: Begin
312: x_progress := 'PO_COMMUNICATION_PVT.PO_NEW_COMMUNICATION';
305: funcmode IN VARCHAR2,
306: resultout OUT NOCOPY VARCHAR2) is
307: x_progress varchar2(100);
308: l_document_subtype po_headers.type_lookup_code%TYPE;
309: l_document_type po_headers.type_lookup_code%TYPE;
310:
311: Begin
312: x_progress := 'PO_COMMUNICATION_PVT.PO_NEW_COMMUNICATION';
313:
372: actid IN NUMBER,
373: funcmode IN VARCHAR2,
374: resultout OUT NOCOPY VARCHAR2) is
375: l_document_id number;
376: l_document_subtype po_headers.type_lookup_code%TYPE;
377: l_revision_num number;
378: l_orgid number;
379: l_entity_name varchar2(30);
380: l_language_code fnd_languages.language_code%type;
378: l_orgid number;
379: l_entity_name varchar2(30);
380: l_language_code fnd_languages.language_code%type;
381: x_progress varchar2(100);
382: l_document_type po_headers.type_lookup_code%TYPE;
383:
384: Begin
385: x_progress := 'PO_COMMUNICATION_PVT.DELETE_PDF_ATTACHMENTS';
386:
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,
789:
790: if p_document_type = 'RELEASE' then
791: select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
792: into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
793: from po_headers poh, po_vendor_sites pvs, po_releases por
794: where pvs.vendor_site_id = poh.vendor_site_id
795: and poh.po_header_id = por.po_header_id
796: and por.po_release_id = p_document_id;
797: else
796: and por.po_release_id = p_document_id;
797: else
798: select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
799: into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
800: from po_headers poh, po_vendor_sites pvs
801: where pvs.vendor_site_id = poh.vendor_site_id
802: and poh.po_header_id = p_document_id;
803: end if;
804:
929: funcmode IN VARCHAR2,
930: resultout OUT NOCOPY VARCHAR2)
931: IS
932:
933: l_document_id po_headers.po_header_id%TYPE;
934: l_revision_num po_headers.revision_num%TYPE;
935: l_document_subtype po_headers.type_lookup_code%TYPE;
936: l_document_type po_headers.type_lookup_code%TYPE;
937: l_territory fnd_languages.nls_territory%type;
930: resultout OUT NOCOPY VARCHAR2)
931: IS
932:
933: l_document_id po_headers.po_header_id%TYPE;
934: l_revision_num po_headers.revision_num%TYPE;
935: l_document_subtype po_headers.type_lookup_code%TYPE;
936: l_document_type po_headers.type_lookup_code%TYPE;
937: l_territory fnd_languages.nls_territory%type;
938: l_language_code fnd_languages.language_code%type;
931: IS
932:
933: l_document_id po_headers.po_header_id%TYPE;
934: l_revision_num po_headers.revision_num%TYPE;
935: l_document_subtype po_headers.type_lookup_code%TYPE;
936: l_document_type po_headers.type_lookup_code%TYPE;
937: l_territory fnd_languages.nls_territory%type;
938: l_language_code fnd_languages.language_code%type;
939: l_supp_lang po_vendor_sites_all.language%TYPE;
932:
933: l_document_id po_headers.po_header_id%TYPE;
934: l_revision_num po_headers.revision_num%TYPE;
935: l_document_subtype po_headers.type_lookup_code%TYPE;
936: l_document_type po_headers.type_lookup_code%TYPE;
937: l_territory fnd_languages.nls_territory%type;
938: l_language_code fnd_languages.language_code%type;
939: l_supp_lang po_vendor_sites_all.language%TYPE;
940: l_language fnd_languages.nls_language%type;
937: l_territory fnd_languages.nls_territory%type;
938: l_language_code fnd_languages.language_code%type;
939: l_supp_lang po_vendor_sites_all.language%TYPE;
940: l_language fnd_languages.nls_language%type;
941: l_authorization_status po_headers.authorization_status%TYPE;
942: l_header_id po_headers.po_header_id%TYPE;
943:
944: l_with_terms varchar2(1);
945: --l_old_request_id number; --Bug 7299381
938: l_language_code fnd_languages.language_code%type;
939: l_supp_lang po_vendor_sites_all.language%TYPE;
940: l_language fnd_languages.nls_language%type;
941: l_authorization_status po_headers.authorization_status%TYPE;
942: l_header_id po_headers.po_header_id%TYPE;
943:
944: l_with_terms varchar2(1);
945: --l_old_request_id number; --Bug 7299381
946: l_request_id number;
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:
1113: resultout OUT NOCOPY VARCHAR2)
1114: IS
1115:
1116: l_document_id number;
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;
1114: IS
1115:
1116: l_document_id number;
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);
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);
1145: fnd_attached_documents ad,
1146: fnd_doc_category_usages dcu,
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
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
1157: OR
1158: (ad.pk1_value in (select from_header_id from po_lines_all
1159: where po_header_id=l_po_header_id
1160: and from_header_id is not null
1161: ) and ad.entity_name='PO_HEADERS')
1162: OR
1163: (ad.pk1_value in (select from_line_id from po_lines_all
1164: where po_header_id=l_po_header_id
1165: and from_line_id is not null
1196: fnd_attachment_functions af,
1197: fnd_lobs fl
1198: where ((ad.pk1_value=to_char((select po_header_id from po_releases_all
1199: where po_release_id=l_po_release_id
1200: )) and ad.entity_name='PO_HEADERS')
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
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
1384: fnd_attachment_functions af,
1385: fnd_lobs fl
1386: where ((ad.pk1_value=to_char((select po_header_id from po_releases_all
1387: where po_release_id=l_document_id
1388: )) and ad.entity_name='PO_HEADERS')
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
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
1445: fnd_attached_documents ad,
1446: fnd_doc_category_usages dcu,
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
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
1457: OR
1458: (ad.pk1_value in (select from_header_id from po_lines_all
1459: where po_header_id=l_document_id
1460: and from_header_id is not null
1461: ) and ad.entity_name='PO_HEADERS')
1462: OR
1463: (ad.pk1_value in (select from_line_id from po_lines_all
1464: where po_header_id=l_document_id
1465: and from_line_id is not null
1638: resultout OUT NOCOPY VARCHAR2)
1639: IS
1640:
1641: l_document_id number;
1642: l_document_subtype po_headers.type_lookup_code%TYPE;
1643: l_document_type po_headers.type_lookup_code%TYPE;
1644: l_revision_num number;
1645: l_request_id number;
1646: l_territory varchar2(30);
1639: IS
1640:
1641: l_document_id number;
1642: l_document_subtype po_headers.type_lookup_code%TYPE;
1643: l_document_type po_headers.type_lookup_code%TYPE;
1644: l_revision_num number;
1645: l_request_id number;
1646: l_territory varchar2(30);
1647: l_set_lang boolean;
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:
1818: resultout OUT NOCOPY VARCHAR2)
1819: IS
1820:
1821: l_document_id number;
1822: l_document_subtype po_headers.type_lookup_code%TYPE;
1823: l_revision_num number;
1824: l_request_id number;
1825: l_language_code varchar2(25);
1826: x_progress varchar2(200);
1967: p_print_flag in varchar2,
1968: p_store_flag in varchar2,
1969: p_request_id out NOCOPY number) is
1970:
1971: l_po_num po_headers.segment1%type := NULL;
1972: l_po_header_id po_headers.po_header_id%type := NULL;
1973: l_po_release_id po_releases.po_release_id%type := NULL;
1974: l_communication varchar2(1);
1975: l_api_name CONSTANT VARCHAR2(25):= 'launch_communicate';
1968: p_store_flag in varchar2,
1969: p_request_id out NOCOPY number) is
1970:
1971: l_po_num po_headers.segment1%type := NULL;
1972: l_po_header_id po_headers.po_header_id%type := NULL;
1973: l_po_release_id po_releases.po_release_id%type := NULL;
1974: l_communication varchar2(1);
1975: l_api_name CONSTANT VARCHAR2(25):= 'launch_communicate';
1976:
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
2480: Begin
2481: IF p_document_type in ('STANDARD','BLANKET','CONTRACT') THEN
2482: select max(revision_num)
2483: into l_revision_num
2484: from po_headers_archive_all
2485: where po_header_id = p_document_id
2486: and authorization_status = 'APPROVED';
2487: ELSE
2488: select max(revision_num)
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
3088: document in out NOCOPY blob,
3089: document_type in out NOCOPY varchar2) IS
3090: l_filename fnd_lobs.file_name%type;
3091: l_document_id number;
3092: l_document_type po_headers.type_lookup_code%TYPE;
3093: l_org_id number;
3094: l_revision_number number;
3095: l_language fnd_languages.language_code%type;
3096: l_entity_name varchar2(30);
3205: document in out nocopy blob,
3206: document_type in out nocopy varchar2) IS
3207: l_filename fnd_lobs.file_name%type;
3208: l_document_id number;
3209: l_document_type po_headers.type_lookup_code%TYPE;
3210: l_org_id number;
3211: l_revision_number number;
3212: l_language fnd_languages.language_code%type;
3213: l_entity_name varchar2(30);
3318: document in out nocopy blob,
3319: document_type in out nocopy varchar2) IS
3320: l_filename fnd_lobs.file_name%type;
3321: l_document_id number;
3322: l_document_type po_headers.type_lookup_code%TYPE;
3323: l_org_id number;
3324: l_revision_number number;
3325: l_language fnd_languages.language_code%type;
3326: l_entity_name varchar2(30);
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
3509: l_okc_file_data fnd_lobs.file_data%TYPE;
3510: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
3511:
3512: l_po_document_id number;
3513: l_po_document_type po_headers.type_lookup_code%TYPE;
3514: l_po_document_subtype po_headers.type_lookup_code%TYPE;
3515: l_po_org_id number;
3516: l_po_revision_number number;
3517: l_language fnd_languages.language_code%type;
3510: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
3511:
3512: l_po_document_id number;
3513: l_po_document_type po_headers.type_lookup_code%TYPE;
3514: l_po_document_subtype po_headers.type_lookup_code%TYPE;
3515: l_po_org_id number;
3516: l_po_revision_number number;
3517: l_language fnd_languages.language_code%type;
3518: l_withTerms varchar2(1);
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;
3977: fnd_attachment_functions af,
3978: fnd_lobs fl
3979: where ((ad.pk1_value=to_char((select po_header_id from po_releases_all
3980: where po_release_id=p_document_id
3981: )) and ad.entity_name='PO_HEADERS')
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
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
4028: fnd_attached_documents ad,
4029: fnd_doc_category_usages dcu,
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
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
4040: OR
4041: (ad.pk1_value in (select from_header_id from po_lines_all
4042: where po_header_id=p_document_id
4043: and from_header_id is not null
4044: ) and ad.entity_name='PO_HEADERS')
4045: OR
4046: (ad.pk1_value in (select from_line_id from po_lines_all
4047: where po_header_id=p_document_id
4048: and from_line_id is not null
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);
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);
4122: l_change_summary PO_HEADERS.CHANGE_SUMMARY%type;
4123: l_timezone HZ_TIMEZONES_VL.NAME%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);
4122: l_change_summary PO_HEADERS.CHANGE_SUMMARY%type;
4123: l_timezone HZ_TIMEZONES_VL.NAME%TYPE;
4124: l_timezone_id varchar2(10);
4125: l_agreement_assign_query varchar2(2001);
4126: l_arc_agreement_assign_query 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:
4246: /*Bug5983107 Modifying the below sql by removing reference to hr_all_organization_units*/
4247:
4248: SELECT vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
4249: INTO l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
4250: FROM po_vendors vn, po_headers_archive_all ph
4251: WHERE vn.vendor_id = ph.vendor_id
4252: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num;
4253:
4254: SELECT count(distinct(plla.SHIP_TO_LOCATION_ID)) INTO PO_COMMUNICATION_PVT.g_dist_shipto_count
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
4472:
4473: --bug#3768142 added the condtion if p_document_type='RELEASE'
4474: --so that the attachments for Releases are also displayed
4475: --correctly. An order by is used so that first the
4476: --PO_HEADERS(BPA) attachments are printed followed by PO_RELEASES
4477: --attachments and then finally PO_VENDORS. This is necessary
4478: --only for the Releases because you can display the BPA header
4479: --attachments also with a release.
4480:
4486: l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
4487: FROM
4488: fnd_attached_docs_form_vl fad,
4489: fnd_documents_short_text fds
4490: WHERE ((entity_name=''PO_HEADERS'' AND
4491: pk1_value=to_char(phx.po_header_id))OR
4492: (entity_name = ''PO_RELEASES'' AND
4493: pk1_value = to_char(phx.po_release_id)) OR
4494: (entity_name = ''PO_VENDORS'' AND
4502: --
4503: l_head_url_attachment_query := 'CURSOR(
4504: SELECT fad.url web_page --Bug#4958642
4505: FROM fnd_attached_docs_form_vl fad
4506: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
4507: (fad.entity_name=''PO_RELEASES'' AND fad.pk1_value=to_char(phx.po_release_id)) OR
4508: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
4509: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
4510: )
4514:
4515: l_head_file_attachment_query := 'CURSOR(
4516: SELECT fad.file_name
4517: FROM fnd_attached_docs_form_vl fad
4518: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
4519: (fad.entity_name=''PO_RELEASES'' AND fad.pk1_value=to_char(phx.po_release_id)) OR
4520: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
4521: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
4522: )
4533: l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
4534: FROM
4535: fnd_attached_docs_form_vl fad,
4536: fnd_documents_short_text fds
4537: WHERE ((entity_name = ''PO_HEADERS'' AND
4538: pk1_value = to_char(phx.po_header_id)) OR
4539: (entity_name = ''PO_VENDORS'' AND
4540: pk1_value = to_char(phx.vendor_id)) OR
4541: (entity_name = ''PO_VENDOR_SITES'' AND
4547: --
4548: l_head_url_attachment_query := 'CURSOR(
4549: SELECT fad.url web_page --Bug#4958642
4550: FROM fnd_attached_docs_form_vl fad
4551: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
4552: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
4553: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
4554: )
4555: AND fad.datatype_id=5
4558:
4559: l_head_file_attachment_query := 'CURSOR(
4560: SELECT fad.file_name
4561: FROM fnd_attached_docs_form_vl fad
4562: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
4563: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
4564: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
4565: )
4566: AND fad.datatype_id=6
4602: SELECT fad.url web_page --Bug#4958642
4603: FROM fnd_attached_docs_form_vl fad
4604: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
4605: OR
4606: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
4607: AND plx.from_header_id IS NOT NULL)
4608: OR
4609: (fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
4610: AND plx.from_line_id IS NOT NULL)
4621: SELECT fad.file_name
4622: FROM fnd_attached_docs_form_vl fad
4623: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
4624: OR
4625: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
4626: AND plx.from_header_id IS NOT NULL)
4627: OR
4628: (fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
4629: AND plx.from_line_id IS NOT NULL)
4958: PO_COMMUNICATION_PVT.getDocumentName() document_name,
4959: PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
4960: fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile,
4961: PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '|| l_xml_message_query || ','|| l_head_short_attachment_query||','||l_head_url_attachment_query||','||l_head_file_attachment_query||'
4962: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
4963:
4964: IF(p_document_subtype <> 'CONTRACTS') THEN -- contracts will have only headers
4965:
4966: SELECT count(*) into l_count FROM po_lines_all WHERE po_header_id = p_document_id;
4986: WHERE plx.po_header_id = phx.po_header_id and not exists
4987: (select ''x'' from po_lines_archive_all plaa where
4988: plaa.po_line_id = plx.po_line_id and
4989: plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
4990: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
4991:
4992: END IF;
4993:
4994: SELECT count(*) into l_count FROM po_line_locations_all WHERE po_header_id = p_document_id;
5021: WHERE plx.po_header_id = phx.po_header_id and not exists
5022: (select ''x'' from po_lines_archive_all plaa where
5023: plaa.po_line_id = plx.po_line_id and
5024: plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
5025: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5026: END IF;
5027:
5028: IF(p_document_subtype <> 'BLANKET') THEN -- blankets will not have distributions
5029: SELECT count(*) into l_count FROM po_distributions_all WHERE po_header_id = p_document_id;
5048: FROM po_lines_xml plx WHERE plx.po_header_id = phx.po_header_id and not exists
5049: (select ''x'' from po_lines_archive_all plaa where
5050: plaa.po_line_id = plx.po_line_id and
5051: plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
5052: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
5053: phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5054:
5055: END IF;
5056: END IF;
5074: PO_COMMUNICATION_PVT.getDocumentName() document_name,
5075: PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
5076: fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
5077: l_xml_message_query || ','|| l_head_short_attachment_query ||','||l_head_url_attachment_query||','||l_head_file_attachment_query||'
5078: FROM PO_HEADERS_ARCHIVE_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5079:
5080: IF(p_document_subtype <> 'CONTRACTS') THEN -- contracts will have only headers
5081:
5082: SELECT count(*) into l_count FROM po_lines_archive_all WHERE po_header_id = p_document_id;
5098: WHERE entity_type='''||l_eventType||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
5099: '|| l_line_short_attachment_query ||','||l_line_url_attachment_query||','||l_line_file_attachment_query||' FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
5100: AND plx.REVISION_NUM = (select max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
5101: and pla.revision_num <= pcgt.revision_number ) and ''Y'' = decode(nvl(plx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(plx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by plx.line_num) AS lines
5102: FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt
5103: WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId()
5104: AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5105:
5106: END IF;
5132: and ''Y'' = decode(nvl(pllx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by pllx.shipment_num ) AS line_locations' || l_complex_lloc_query || '
5133: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
5134: AND plx.REVISION_NUM = (select max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
5135: and pla.revision_num <= pcgt.revision_number ) and ''Y'' = decode(nvl(plx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(plx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by plx.line_num) AS lines
5136: FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5137: END IF;
5138:
5139: IF(p_document_subtype <> 'BLANKET') THEN -- blankets will not have distributions
5140: SELECT count(*) into l_count FROM po_distributions_archive_all WHERE po_header_id = p_document_id;
5161: and ''Y'' = decode(nvl(pllx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by pllx.shipment_num ) AS line_locations'|| l_complex_dist_query || '
5162: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
5163: AND plx.REVISION_NUM = (select max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
5164: and pla.revision_num <= pcgt.revision_number ) and ''Y'' = decode(nvl(plx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(plx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by plx.line_num) AS lines
5165: FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
5166: phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5167:
5168: END IF; -- end of
5169: END IF; -- end of balnket if condition
5262: and ''Y'' = decode(nvl(pllaa.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllaa.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) )
5263: and plx.po_header_id = phx.po_header_id
5264: AND plx.REVISION_NUM = (select max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
5265: and pla.revision_num <= (select max(pb.revision_num)
5266: from po_headers_archive_all pb, po_releases_archive_all pr
5267: where pb.po_header_id = pr.po_header_id
5268: and pr.po_release_id = pcgt.po_release_id
5269: and pr.revision_num= pcgt.revision_number
5270: and pb.approved_date <= pr.approved_date
5282: /* for header long text */
5283: --bug#3768142 also added the condition to check if the document
5284: --type is a release so that even the release header documents
5285: --are retrieved. An order by is used so that first the
5286: --PO_HEADERS(BPA) attachments are printed followed by PO_RELEASES
5287: --attachments and then finally PO_VENDORS. This is necessary
5288: --only for the Releases because you can display the BPA header
5289: --attachments also with a release.
5290: --bug#3823799: Replaced the hard coded p_document id with PO_COMMUNICATION_PVT.getDocumentId() function
5302: fnd_attached_docs_form_vl fad,
5303: fnd_documents_long_text fdl
5304: WHERE ( (entity_name=''PO_RELEASES'' AND
5305: pk1_value= to_char(PO_COMMUNICATION_PVT.getDocumentId()) ) OR
5306: (entity_name = ''PO_HEADERS'' AND
5307: pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
5308: (entity_name = ''PO_VENDORS'' AND
5309: pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
5310: function_name = ''PO_PRINTPO''
5324: l_headerAttachmentsQuery := 'select PO_COMMUNICATION_PVT.get_clob(fdl.rowid) long_text
5325: FROM
5326: fnd_attached_docs_form_vl fad,
5327: fnd_documents_long_text fdl
5328: WHERE ((entity_name = ''PO_HEADERS'' AND
5329: pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
5330: (entity_name = ''PO_VENDORS'' AND
5331: pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
5332: function_name = ''PO_PRINTPO''
5913: --toreturn the values in global variables
5914: --po_communication_pvt.g_town_or_city
5915: --po_communication_pvt.g_postal_code
5916: --and po_communication_pvt.g_state_or_province.
5917: --These functions are called by the PO_HEADERS_CHANGE_PRINT
5918: --report
5919:
5920: function getTownOrCity return varchar2 is
5921: begin
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:
6182: PO_COMMUNICATION_PVT.g_arcBuyer_title, PO_COMMUNICATION_PVT.g_arcAgent_id
6183:
6184: FROM
6185: PER_ALL_PEOPLE_F HRE,
6186: PO_HEADERS_ARCHIVE_ALL PHA
6187: WHERE
6188: HRE.PERSON_ID = PHA.AGENT_ID AND
6189: --HRE.EMPLOYEE_NUMBER IS NOT NULL AND --
6190: TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND
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:
6993: d_progress := 20;
6994:
6995: SELECT poha.style_id
6996: INTO l_style_id
6997: FROM po_headers_archive_all poha
6998: WHERE poha.po_header_id = p_document_id
6999: AND poha.revision_num = p_revision_num;
7000:
7001: d_progress := 25;
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: