[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
105: g_legal_entity_town_or_city HR_LOCATIONS.TOWN_OR_CITY%type := null;
106: g_legal_entity_state HR_LOCATIONS.REGION_1%type := null;
107: g_legal_entity_postal_code HR_LOCATIONS.POSTAL_CODE%type := null;
108: g_legal_entity_country FND_TERRITORIES_TL.TERRITORY_SHORT_NAME%type := null;
109: g_legal_entity_org_id PO_HEADERS_ALL.ORG_ID%type := null;
110:
111: -- End of Legal Entity details ----
112:
113: g_dist_shipto_count number := NULL ; -- Variable which holds count of distinct shipment ship to ids
113: g_dist_shipto_count number := NULL ; -- Variable which holds count of distinct shipment ship to ids
114: g_line_org_amount number := NULL;
115:
116: /*Bug#35833910 the variable determines whether the po has Terms and Conditions */
117: g_with_terms po_headers_all.conterms_exist_flag%type;
118:
119: --
120: g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
121: g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
180: resultout OUT NOCOPY VARCHAR2)
181: IS
182:
183: l_document_id number;
184: l_document_subtype po_headers.type_lookup_code%TYPE;
185: --Bug13774287
186: l_document_type po_headers.type_lookup_code%TYPE;
187: --END Bug13774287
188: l_revision_num number;
182:
183: l_document_id number;
184: l_document_subtype po_headers.type_lookup_code%TYPE;
185: --Bug13774287
186: l_document_type po_headers.type_lookup_code%TYPE;
187: --END Bug13774287
188: l_revision_num number;
189: l_request_id number;
190: l_authorization_status varchar2(25);
188: l_revision_num number;
189: l_request_id number;
190: l_authorization_status varchar2(25);
191: x_progress varchar2(100);
192: l_with_terms PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
193: l_set_lang boolean;
194: l_draft_id number; -- CLM
195: --
196: l_language_code fnd_languages.language_code%type;
390: actid IN NUMBER,
391: funcmode IN VARCHAR2,
392: resultout OUT NOCOPY VARCHAR2) is
393: x_progress varchar2(100);
394: l_document_subtype po_headers.type_lookup_code%TYPE;
395: l_document_type po_headers.type_lookup_code%TYPE;
396:
397: Begin
398: x_progress := 'PO_COMMUNICATION_PVT.PO_NEW_COMMUNICATION';
391: funcmode IN VARCHAR2,
392: resultout OUT NOCOPY VARCHAR2) is
393: x_progress varchar2(100);
394: l_document_subtype po_headers.type_lookup_code%TYPE;
395: l_document_type po_headers.type_lookup_code%TYPE;
396:
397: Begin
398: x_progress := 'PO_COMMUNICATION_PVT.PO_NEW_COMMUNICATION';
399:
458: actid IN NUMBER,
459: funcmode IN VARCHAR2,
460: resultout OUT NOCOPY VARCHAR2) is
461: l_document_id number;
462: l_document_subtype po_headers.type_lookup_code%TYPE;
463: l_revision_num number;
464: l_draft_id number; --CLM
465: l_draft_id_char varchar2(30); --CLM
466: l_orgid number;
466: l_orgid number;
467: l_entity_name varchar2(30);
468: l_language_code fnd_languages.language_code%type;
469: x_progress varchar2(100);
470: l_document_type po_headers.type_lookup_code%TYPE;
471:
472: Begin
473: x_progress := 'PO_COMMUNICATION_PVT.DELETE_PDF_ATTACHMENTS';
474:
565:
566: l_language_code fnd_languages.language_code%type;
567: l_document_id number;
568: l_revision_num number;
569: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
570: l_document_subtype po_headers_all.type_lookup_code%TYPE;
571: l_document_type po_headers_all.type_lookup_code%TYPE;
572: l_count number;
573: l_filename fnd_lobs.file_name%type;
566: l_language_code fnd_languages.language_code%type;
567: l_document_id number;
568: l_revision_num number;
569: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
570: l_document_subtype po_headers_all.type_lookup_code%TYPE;
571: l_document_type po_headers_all.type_lookup_code%TYPE;
572: l_count number;
573: l_filename fnd_lobs.file_name%type;
574: l_orgid number;
567: l_document_id number;
568: l_revision_num number;
569: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
570: l_document_subtype po_headers_all.type_lookup_code%TYPE;
571: l_document_type po_headers_all.type_lookup_code%TYPE;
572: l_count number;
573: l_filename fnd_lobs.file_name%type;
574: l_orgid number;
575: x_progress varchar2(100);
572: l_count number;
573: l_filename fnd_lobs.file_name%type;
574: l_orgid number;
575: x_progress varchar2(100);
576: l_with_terms PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
577: l_terms varchar2(10);
578:
579: Begin
580: x_progress := 'PO_COMMUNICATION_PVT.PO_PDF_EXISTS';
725: --l_legal_name hr_all_organization_units.name%TYPE;
726: --bug##3682458 replaced legal entity name with operating unit
727: l_operating_unit hr_all_organization_units.name%TYPE;
728:
729: l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
730: l_docNumber PO_HEADERS_ALL.SEGMENT1%TYPE;
731: l_doc_num_rel varchar2(30);
732: l_release_num PO_RELEASES.release_num%TYPE; -- Bug 3215186;
733: l_ga_flag varchar2(1) := null; -- Bug # 3290385
726: --bug##3682458 replaced legal entity name with operating unit
727: l_operating_unit hr_all_organization_units.name%TYPE;
728:
729: l_document_id PO_HEADERS_ALL.po_header_id%TYPE;
730: l_docNumber PO_HEADERS_ALL.SEGMENT1%TYPE;
731: l_doc_num_rel varchar2(30);
732: l_release_num PO_RELEASES.release_num%TYPE; -- Bug 3215186;
733: l_ga_flag varchar2(1) := null; -- Bug # 3290385
734: l_doc_display_name FND_NEW_MESSAGES.message_text%TYPE; -- Bug 3215186
845: l_document_id := p_document_id;
846: end if;
847:
848: select segment1, global_agreement_flag into l_docNumber, l_ga_flag
849: from po_headers_all
850: where po_header_id = l_document_id;
851:
852: wf_engine.SetItemAttrText (itemtype => l_itemtype,
853: itemkey => l_itemkey,
898:
899: if p_document_type = 'RELEASE' then
900: select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
901: into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
902: from po_headers poh, po_vendor_sites pvs, po_releases por
903: where pvs.vendor_site_id = poh.vendor_site_id
904: and poh.po_header_id = por.po_header_id
905: and por.po_release_id = p_document_id;
906: else
905: and por.po_release_id = p_document_id;
906: else
907: select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
908: into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
909: from po_headers poh, po_vendor_sites pvs
910: where pvs.vendor_site_id = poh.vendor_site_id
911: and poh.po_header_id = p_document_id;
912: end if;
913:
1074: funcmode IN VARCHAR2,
1075: resultout OUT NOCOPY VARCHAR2)
1076: IS
1077:
1078: l_document_id po_headers.po_header_id%TYPE;
1079: l_revision_num po_headers.revision_num%TYPE;
1080: l_document_subtype po_headers.type_lookup_code%TYPE;
1081: l_document_type po_headers.type_lookup_code%TYPE;
1082: l_territory fnd_languages.nls_territory%type;
1075: resultout OUT NOCOPY VARCHAR2)
1076: IS
1077:
1078: l_document_id po_headers.po_header_id%TYPE;
1079: l_revision_num po_headers.revision_num%TYPE;
1080: l_document_subtype po_headers.type_lookup_code%TYPE;
1081: l_document_type po_headers.type_lookup_code%TYPE;
1082: l_territory fnd_languages.nls_territory%type;
1083: l_language_code fnd_languages.language_code%type;
1076: IS
1077:
1078: l_document_id po_headers.po_header_id%TYPE;
1079: l_revision_num po_headers.revision_num%TYPE;
1080: l_document_subtype po_headers.type_lookup_code%TYPE;
1081: l_document_type po_headers.type_lookup_code%TYPE;
1082: l_territory fnd_languages.nls_territory%type;
1083: l_language_code fnd_languages.language_code%type;
1084: l_supp_lang po_vendor_sites_all.language%TYPE;
1077:
1078: l_document_id po_headers.po_header_id%TYPE;
1079: l_revision_num po_headers.revision_num%TYPE;
1080: l_document_subtype po_headers.type_lookup_code%TYPE;
1081: l_document_type po_headers.type_lookup_code%TYPE;
1082: l_territory fnd_languages.nls_territory%type;
1083: l_language_code fnd_languages.language_code%type;
1084: l_supp_lang po_vendor_sites_all.language%TYPE;
1085: l_language fnd_languages.nls_language%type;
1082: l_territory fnd_languages.nls_territory%type;
1083: l_language_code fnd_languages.language_code%type;
1084: l_supp_lang po_vendor_sites_all.language%TYPE;
1085: l_language fnd_languages.nls_language%type;
1086: l_authorization_status po_headers.authorization_status%TYPE;
1087: l_header_id po_headers.po_header_id%TYPE;
1088:
1089: l_with_terms varchar2(1);
1090: --l_old_request_id number; --Bug 7299381
1083: l_language_code fnd_languages.language_code%type;
1084: l_supp_lang po_vendor_sites_all.language%TYPE;
1085: l_language fnd_languages.nls_language%type;
1086: l_authorization_status po_headers.authorization_status%TYPE;
1087: l_header_id po_headers.po_header_id%TYPE;
1088:
1089: l_with_terms varchar2(1);
1090: --l_old_request_id number; --Bug 7299381
1091: l_request_id number;
1158:
1159: SELECT pv.language
1160: INTO l_supp_lang
1161: FROM po_vendor_sites_all pv,
1162: po_headers_all ph
1163: WHERE ph.po_header_id = l_header_id
1164: AND ph.vendor_site_id = pv.vendor_site_id;
1165:
1166: EXCEPTION
1324: resultout OUT NOCOPY VARCHAR2)
1325: IS
1326:
1327: l_document_id number;
1328: l_document_subtype po_headers.type_lookup_code%TYPE;
1329: l_document_type po_headers.type_lookup_code%TYPE;
1330: l_revision_num number;
1331: l_request_id number;
1332: l_conterm_exists PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
1325: IS
1326:
1327: l_document_id number;
1328: l_document_subtype po_headers.type_lookup_code%TYPE;
1329: l_document_type po_headers.type_lookup_code%TYPE;
1330: l_revision_num number;
1331: l_request_id number;
1332: l_conterm_exists PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
1333: l_authorization_status varchar2(25);
1328: l_document_subtype po_headers.type_lookup_code%TYPE;
1329: l_document_type po_headers.type_lookup_code%TYPE;
1330: l_revision_num number;
1331: l_request_id number;
1332: l_conterm_exists PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
1333: l_authorization_status varchar2(25);
1334: l_progress varchar2(200);
1335: --l_old_request_id number; --Bug 7299381
1336: l_withterms varchar2(1);
1365: fnd_attached_documents ad,
1366: fnd_doc_category_usages dcu,
1367: fnd_attachment_functions af,
1368: fnd_lobs fl
1369: where ((ad.pk1_value = to_char(l_po_header_id) and ad.entity_name = 'PO_HEADERS')
1370: OR
1371: (ad.pk1_value = to_char((select vendor_id from po_headers_all
1372: where po_header_id = l_po_header_id)) and ad.entity_name = 'PO_VENDORS')
1373: OR
1367: fnd_attachment_functions af,
1368: fnd_lobs fl
1369: where ((ad.pk1_value = to_char(l_po_header_id) and ad.entity_name = 'PO_HEADERS')
1370: OR
1371: (ad.pk1_value = to_char((select vendor_id from po_headers_all
1372: where po_header_id = l_po_header_id)) and ad.entity_name = 'PO_VENDORS')
1373: OR
1374: (ad.pk1_value in (select to_char(po_line_id) from po_lines_all
1375: where po_header_id = l_po_header_id
1377: OR
1378: (ad.pk1_value in (select to_char(from_header_id) from po_lines_all
1379: where po_header_id = l_po_header_id
1380: and from_header_id is not null
1381: ) and ad.entity_name = 'PO_HEADERS')
1382: OR
1383: (ad.pk1_value in (select to_char(from_line_id) from po_lines_all
1384: where po_header_id = l_po_header_id
1385: and from_line_id is not null
1416: fnd_attachment_functions af,
1417: fnd_lobs fl
1418: where ((ad.pk1_value = to_char((select po_header_id from po_releases_all
1419: where po_release_id = l_po_release_id
1420: )) and ad.entity_name = 'PO_HEADERS')
1421: OR
1422: (ad.pk1_value = to_char(l_po_release_id) and ad.entity_name = 'PO_RELEASES')
1423: OR
1424: (ad.pk1_value = to_char((select pha.vendor_id
1421: OR
1422: (ad.pk1_value = to_char(l_po_release_id) and ad.entity_name = 'PO_RELEASES')
1423: OR
1424: (ad.pk1_value = to_char((select pha.vendor_id
1425: from po_headers_all pha, po_releases_all pra
1426: where pra.po_release_id = l_po_release_id
1427: and pha.po_header_id = pra.po_header_id
1428: )) and ad.entity_name = 'PO_VENDORS')
1429: OR
1630: fnd_attachment_functions af,
1631: fnd_lobs fl
1632: where ((ad.pk1_value = to_char((select po_header_id from po_releases_all
1633: where po_release_id = l_document_id
1634: )) and ad.entity_name = 'PO_HEADERS')
1635: OR
1636: (ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_RELEASES')
1637: OR
1638: (ad.pk1_value = (select pha.vendor_id
1635: OR
1636: (ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_RELEASES')
1637: OR
1638: (ad.pk1_value = (select pha.vendor_id
1639: from po_headers_all pha, po_releases_all pra
1640: where pra.po_release_id = l_document_id
1641: and pha.po_header_id = pra.po_header_id
1642: ) and ad.entity_name = 'PO_VENDORS')
1643: OR
1691: fnd_attached_documents ad,
1692: fnd_doc_category_usages dcu,
1693: fnd_attachment_functions af,
1694: fnd_lobs fl
1695: where ((ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_HEADERS')
1696: OR
1697: (ad.pk1_value = to_char((select vendor_id from po_headers_all
1698: where po_header_id = l_document_id)) and ad.entity_name = 'PO_VENDORS')
1699: OR
1693: fnd_attachment_functions af,
1694: fnd_lobs fl
1695: where ((ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_HEADERS')
1696: OR
1697: (ad.pk1_value = to_char((select vendor_id from po_headers_all
1698: where po_header_id = l_document_id)) and ad.entity_name = 'PO_VENDORS')
1699: OR
1700: (ad.pk1_value in (select to_char(po_line_id) from po_lines_all
1701: where po_header_id = l_document_id
1703: OR
1704: (ad.pk1_value in (select to_char(from_header_id) from po_lines_all
1705: where po_header_id = l_document_id
1706: and from_header_id is not null
1707: ) and ad.entity_name = 'PO_HEADERS')
1708: OR
1709: (ad.pk1_value in (select to_char(from_line_id) from po_lines_all
1710: where po_header_id = l_document_id
1711: and from_line_id is not null
1898: funcmode IN VARCHAR2,
1899: resultout OUT NOCOPY VARCHAR2)
1900: IS
1901: l_document_id number;
1902: l_document_subtype po_headers.type_lookup_code%TYPE;
1903: l_document_type po_headers.type_lookup_code%TYPE;
1904: l_revision_num number;
1905: l_request_id number;
1906: l_territory varchar2(30);
1899: resultout OUT NOCOPY VARCHAR2)
1900: IS
1901: l_document_id number;
1902: l_document_subtype po_headers.type_lookup_code%TYPE;
1903: l_document_type po_headers.type_lookup_code%TYPE;
1904: l_revision_num number;
1905: l_request_id number;
1906: l_territory varchar2(30);
1907: l_set_lang boolean;
1989: END IF;
1990:
1991: BEGIN
1992: SELECT pv.language into l_supp_lang
1993: FROM po_vendor_sites_all pv, po_headers_all ph
1994: WHERE
1995: ph.po_header_id = l_header_id and ph.vendor_site_id = pv.vendor_site_id;
1996: EXCEPTION
1997: WHEN NO_DATA_FOUND THEN
2144: resultout OUT NOCOPY VARCHAR2)
2145: IS
2146:
2147: l_document_id number;
2148: l_document_subtype po_headers.type_lookup_code%TYPE;
2149: --Bug13774287
2150: l_document_type po_headers.type_lookup_code%TYPE;
2151: --End Bug13774287
2152: l_revision_num number;
2146:
2147: l_document_id number;
2148: l_document_subtype po_headers.type_lookup_code%TYPE;
2149: --Bug13774287
2150: l_document_type po_headers.type_lookup_code%TYPE;
2151: --End Bug13774287
2152: l_revision_num number;
2153: l_request_id number;
2154: l_language_code varchar2(25);
2357: p_print_flag in varchar2,
2358: p_store_flag in varchar2,
2359: p_request_id out NOCOPY number) is
2360:
2361: l_po_num po_headers.segment1%type := NULL;
2362: l_po_header_id po_headers.po_header_id%type := NULL;
2363: l_po_release_id po_releases.po_release_id%type := NULL;
2364: l_communication varchar2(1);
2365: l_api_name CONSTANT VARCHAR2(25) := 'launch_communicate';
2358: p_store_flag in varchar2,
2359: p_request_id out NOCOPY number) is
2360:
2361: l_po_num po_headers.segment1%type := NULL;
2362: l_po_header_id po_headers.po_header_id%type := NULL;
2363: l_po_release_id po_releases.po_release_id%type := NULL;
2364: l_communication varchar2(1);
2365: l_api_name CONSTANT VARCHAR2(25) := 'launch_communicate';
2366:
2632: p_email_address in varchar2,
2633: p_request_id out nocopy number)
2634: IS
2635:
2636: l_conterm_exists_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
2637: l_supp_lang fnd_languages.nls_language%type;
2638: l_territory fnd_languages.nls_territory%type;
2639: l_revision_num number;
2640: l_set_lang boolean;
2706: l_progress := '010';
2707: begin
2708: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
2709: l_entity_name := 'PO_HEAD';
2710: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2711: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2712: else
2713: l_entity_name := 'PO_REL';
2714: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
2710: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
2711: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
2712: else
2713: l_entity_name := 'PO_REL';
2714: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
2715: where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
2716: ph.vendor_site_id = pvs.vendor_site_id ;
2717: end if;
2718: IF g_debug_stmt THEN
2736: end if;
2737:
2738: l_buyer_language_code := userenv('LANG');
2739: begin
2740: select NVL(conterms_exist_flag, 'N') into l_conterm_exists_flag from po_headers_all
2741: where
2742: po_header_id = p_document_id and revision_num = p_revision_number;
2743:
2744: exception
2868: Begin
2869: IF p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') THEN
2870: select max(revision_num)
2871: into l_revision_num
2872: from po_headers_archive_all
2873: where po_header_id = p_document_id
2874: and authorization_status = 'APPROVED';
2875: ELSE
2876: select max(revision_num)
3106: end if;
3107:
3108: l_progress := '200';
3109: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
3110: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
3111: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
3112: else
3113: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
3114: where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
3109: if p_document_type in ('STANDARD', 'BLANKET', 'CONTRACT') then
3110: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
3111: where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
3112: else
3113: select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
3114: where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
3115: ph.vendor_site_id = pvs.vendor_site_id ;
3116: end if;
3117:
3263:
3264: --
3265: l_file_name fnd_lobs.file_name%type;
3266: l_file_content_type fnd_lobs.file_content_type%type;
3267: l_org_id po_headers_all.org_id%type;
3268: l_api_name CONSTANT VARCHAR2(25) := 'Store_Blob';
3269: l_progress VARCHAR2(3);
3270:
3271: Begin
3295: END IF;
3296:
3297: --Start of code changes for the bug 12799972
3298: IF (l_org_id IS null) THEN
3299: SELECT org_id INTO l_org_id FROM po_headers_all WHERE po_header_id = p_document_id;
3300: po_moac_utils_pvt.SET_POLICY_CONTEXT('S', l_org_id );
3301: END IF;
3302: -- End of code changes for the bug 12799972
3303:
3513: document in out NOCOPY blob,
3514: document_type in out NOCOPY varchar2) IS
3515: l_filename fnd_lobs.file_name%type;
3516: l_document_id number;
3517: l_document_type po_headers.type_lookup_code%TYPE;
3518: l_org_id number;
3519: l_revision_number number;
3520: l_language fnd_languages.language_code%type;
3521: l_entity_name varchar2(30);
3682: document in out nocopy blob,
3683: document_type in out nocopy varchar2) IS
3684: l_filename fnd_lobs.file_name%type;
3685: l_document_id number;
3686: l_document_type po_headers.type_lookup_code%TYPE;
3687: l_org_id number;
3688: l_revision_number number;
3689: l_language fnd_languages.language_code%type;
3690: l_entity_name varchar2(30);
3797: document in out nocopy blob,
3798: document_type in out nocopy varchar2) IS
3799: l_filename fnd_lobs.file_name%type;
3800: l_document_id number;
3801: l_document_type po_headers.type_lookup_code%TYPE;
3802: l_org_id number;
3803: l_revision_number number;
3804: l_language fnd_languages.language_code%type;
3805: l_entity_name varchar2(30);
3864: Begin
3865:
3866: if l_document_type in ('PO', 'PA') then
3867: select fl.language_code into l_language
3868: from po_vendor_sites_all pvs, po_headers_all ph, fnd_languages fl
3869: where ph.vendor_site_id = pvs.vendor_site_id
3870: and ph.po_header_id = l_document_id
3871: and pvs.language = fl.nls_language;
3872: elsif l_document_type = 'RELEASE' then
3870: and ph.po_header_id = l_document_id
3871: and pvs.language = fl.nls_language;
3872: elsif l_document_type = 'RELEASE' then
3873: select fl.language_code into l_language
3874: from po_vendor_sites_all pvs , po_headers_all ph,
3875: po_releases_all pr, fnd_languages fl
3876: where ph.po_header_id = pr.po_header_id
3877: and pr.po_release_id = l_document_id
3878: and ph.vendor_site_id = pvs.vendor_site_id
4044: l_okc_file_data fnd_lobs.file_data%TYPE;
4045: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
4046:
4047: l_po_document_id number;
4048: l_po_document_type po_headers.type_lookup_code%TYPE;
4049: l_po_document_subtype po_headers.type_lookup_code%TYPE;
4050: l_po_org_id number;
4051: l_po_revision_number number;
4052: l_language fnd_languages.language_code%type;
4045: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
4046:
4047: l_po_document_id number;
4048: l_po_document_type po_headers.type_lookup_code%TYPE;
4049: l_po_document_subtype po_headers.type_lookup_code%TYPE;
4050: l_po_org_id number;
4051: l_po_revision_number number;
4052: l_language fnd_languages.language_code%type;
4053: l_withTerms varchar2(1);
4052: l_language fnd_languages.language_code%type;
4053: l_withTerms varchar2(1);
4054:
4055: l_itemtype po_document_types.wf_approval_itemtype%type;
4056: l_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
4057: l_message FND_NEW_MESSAGES.message_text%TYPE;
4058:
4059: l_okc_doc_length number; -- Bug 4173198
4060:
4231: content_type in varchar2,
4232: document in out nocopy blob,
4233: document_type in out nocopy varchar2) IS
4234: l_filename fnd_lobs.file_name%type;
4235: l_document_id po_headers_all.po_header_id%type;
4236: l_document_number po_headers_all.segment1%type;
4237: l_document_type po_headers_all.type_lookup_code%TYPE;
4238: l_org_id po_headers_all.org_id%type;
4239: l_revision_number po_headers_all.revision_num%type;
4232: document in out nocopy blob,
4233: document_type in out nocopy varchar2) IS
4234: l_filename fnd_lobs.file_name%type;
4235: l_document_id po_headers_all.po_header_id%type;
4236: l_document_number po_headers_all.segment1%type;
4237: l_document_type po_headers_all.type_lookup_code%TYPE;
4238: l_org_id po_headers_all.org_id%type;
4239: l_revision_number po_headers_all.revision_num%type;
4240: l_language fnd_languages.language_code%type;
4233: document_type in out nocopy varchar2) IS
4234: l_filename fnd_lobs.file_name%type;
4235: l_document_id po_headers_all.po_header_id%type;
4236: l_document_number po_headers_all.segment1%type;
4237: l_document_type po_headers_all.type_lookup_code%TYPE;
4238: l_org_id po_headers_all.org_id%type;
4239: l_revision_number po_headers_all.revision_num%type;
4240: l_language fnd_languages.language_code%type;
4241: l_entity_name fnd_attached_documents.entity_name%type;
4234: l_filename fnd_lobs.file_name%type;
4235: l_document_id po_headers_all.po_header_id%type;
4236: l_document_number po_headers_all.segment1%type;
4237: l_document_type po_headers_all.type_lookup_code%TYPE;
4238: l_org_id po_headers_all.org_id%type;
4239: l_revision_number po_headers_all.revision_num%type;
4240: l_language fnd_languages.language_code%type;
4241: l_entity_name fnd_attached_documents.entity_name%type;
4242: l_itemtype wf_items.item_type%type;
4235: l_document_id po_headers_all.po_header_id%type;
4236: l_document_number po_headers_all.segment1%type;
4237: l_document_type po_headers_all.type_lookup_code%TYPE;
4238: l_org_id po_headers_all.org_id%type;
4239: l_revision_number po_headers_all.revision_num%type;
4240: l_language fnd_languages.language_code%type;
4241: l_entity_name fnd_attached_documents.entity_name%type;
4242: l_itemtype wf_items.item_type%type;
4243: l_itemkey wf_items.item_key%type;
4532: fnd_attachment_functions af,
4533: fnd_lobs fl
4534: where (ad.pk1_value=to_char((select po_header_id from po_releases_all
4535: where po_release_id=p_document_id
4536: ))and ad.entity_name='PO_HEADERS')
4537:
4538: and d.document_id = ad.document_id
4539: and dcu.category_id = d.category_id
4540: and dcu.attachment_function_id = af.attachment_function_id
4570: fnd_attachment_functions af,
4571: fnd_lobs fl
4572: where (
4573: (ad.pk1_value=to_char((select pha.vendor_id
4574: from po_headers_all pha,po_releases_all pra
4575: where pra.po_release_id=p_document_id
4576: and pha.po_header_id=pra.po_header_id
4577: )) and ad.entity_name='PO_VENDORS'))
4578: and d.document_id = ad.document_id
4664: fnd_attached_documents ad,
4665: fnd_doc_category_usages dcu,
4666: fnd_attachment_functions af,
4667: fnd_lobs fl
4668: where (ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_HEADERS')
4669: and d.document_id = ad.document_id
4670: and dcu.category_id = d.category_id
4671: and dcu.attachment_function_id = af.attachment_function_id
4672: and d.datatype_id=6
4682: fnd_doc_category_usages dcu,
4683: fnd_attachment_functions af,
4684: fnd_lobs fl
4685: where (
4686: (ad.pk1_value=to_char((select vendor_id from po_headers_all
4687: where po_header_id=p_document_id)) and ad.entity_name='PO_VENDORS'))
4688:
4689: and d.document_id = ad.document_id
4690: and dcu.category_id = d.category_id
4726: where (
4727: (ad.pk1_value in (select from_header_id from po_lines_all
4728: where po_header_id=p_document_id
4729: and from_header_id is not null
4730: ) and ad.entity_name='PO_HEADERS'))
4731:
4732: and d.document_id = ad.document_id
4733: and dcu.category_id = d.category_id
4734: and dcu.attachment_function_id = af.attachment_function_id
4856: l_line_Attachments clob;
4857: l_shipmentAttachmentQuery varchar2(1200); --Bug5213932 increase length
4858: l_disAttachments clob;
4859: l_time varchar2(50);
4860: l_vendor_id PO_HEADERS_ALL.vendor_id%type;
4861: l_release_header_id PO_HEADERS_ALL.po_header_id%type;
4862: l_supp_org PO_VENDORS.VENDOR_NAME%type;
4863: l_po_number PO_HEADERS.SEGMENT1%type;
4864: l_message varchar2(2001);
4857: l_shipmentAttachmentQuery varchar2(1200); --Bug5213932 increase length
4858: l_disAttachments clob;
4859: l_time varchar2(50);
4860: l_vendor_id PO_HEADERS_ALL.vendor_id%type;
4861: l_release_header_id PO_HEADERS_ALL.po_header_id%type;
4862: l_supp_org PO_VENDORS.VENDOR_NAME%type;
4863: l_po_number PO_HEADERS.SEGMENT1%type;
4864: l_message varchar2(2001);
4865: l_ammendment_message varchar2(2001);
4859: l_time varchar2(50);
4860: l_vendor_id PO_HEADERS_ALL.vendor_id%type;
4861: l_release_header_id PO_HEADERS_ALL.po_header_id%type;
4862: l_supp_org PO_VENDORS.VENDOR_NAME%type;
4863: l_po_number PO_HEADERS.SEGMENT1%type;
4864: l_message varchar2(2001);
4865: l_ammendment_message varchar2(2001);
4866: l_change_summary PO_HEADERS.CHANGE_SUMMARY%type;
4867: l_timezone HZ_TIMEZONES_VL.NAME%TYPE;
4862: l_supp_org PO_VENDORS.VENDOR_NAME%type;
4863: l_po_number PO_HEADERS.SEGMENT1%type;
4864: l_message varchar2(2001);
4865: l_ammendment_message varchar2(2001);
4866: l_change_summary PO_HEADERS.CHANGE_SUMMARY%type;
4867: l_timezone HZ_TIMEZONES_VL.NAME%TYPE;
4868: l_timezone_id varchar2(10);
4869: l_agreement_assign_query varchar2(2001);
4870: l_arc_agreement_assign_query varchar2(2001);
4944: l_org_id := po_moac_utils_pvt.get_current_org_id;
4945:
4946: IF(l_org_id IS null) Then
4947:
4948: SELECT org_id INTO l_org_id FROM po_headers_all WHERE po_header_id = p_document_id;
4949: po_moac_utils_pvt.SET_POLICY_CONTEXT('S', l_org_id );
4950:
4951: END IF;
4952: --Bug 9391634 org_id was not set because of which fetching data from PO_DOCUMENT_TYPES_TL give error
4980:
4981: /*Bug5983107 Commenting out the below sql and writing the new sql below without reference to hr_all_organization_units*/
4982: /* SELECT hle.name, vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
4983: INTO po_communication_pvt.g_buyer_org, l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
4984: FROM hr_all_organization_units hle, po_vendors vn, po_headers_all ph
4985: WHERE to_char(hle.organization_id) = (select org_information2 from hr_organization_information where
4986: org_information_context = 'Operating Unit Information' and organization_id = ph.org_id) AND vn.vendor_id = ph.vendor_id
4987: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num; */
4988:
4987: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num; */
4988:
4989: SELECT vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
4990: INTO l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
4991: FROM po_vendors vn, po_headers_all ph
4992: WHERE vn.vendor_id = ph.vendor_id
4993: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num;
4994:
4995:
5004: /*Bug5983107 Modifying the below sql by removing reference to hr_all_organization_units*/
5005:
5006: SELECT vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
5007: INTO l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
5008: FROM po_vendors vn, po_headers_archive_all ph
5009: WHERE vn.vendor_id = ph.vendor_id
5010: AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num;
5011:
5012: SELECT count(distinct(plla.SHIP_TO_LOCATION_ID)) INTO PO_COMMUNICATION_PVT.g_dist_shipto_count
5018: END IF;
5019:
5020: /*Bug5983107 Use the below API to get the legal entity name */
5021: BEGIN
5022: select org_id into l_org_id from po_headers_all where po_header_id = p_document_id ;
5023: l_legal_entity_id := PO_CORE_S.get_default_legal_entity_id(l_org_id);
5024:
5025: XLE_UTILITIES_GRP.Get_LegalEntity_Info(
5026: x_return_status,
5052: -- select the header id into g_release_header_id global variable for a given release id.
5053: SELECT po_header_id INTO PO_COMMUNICATION_PVT.g_release_header_id FROM po_releases_all WHERE po_release_id = p_document_id;
5054:
5055: SELECT ph.vendor_id, ph.currency_code INTO l_vendor_id, g_current_currency_code
5056: FROM po_vendors vn, po_headers_all ph
5057: WHERE vn.vendor_id = ph.vendor_id
5058: AND ph.po_header_id = PO_COMMUNICATION_PVT.g_release_header_id ;
5059:
5060: IF p_which_tables = 'MAIN' THEN
5230:
5231: --bug#3768142 added the condtion if p_document_type='RELEASE'
5232: --so that the attachments for Releases are also displayed
5233: --correctly. An order by is used so that first the
5234: --PO_HEADERS(BPA) attachments are printed followed by PO_RELEASES
5235: --attachments and then finally PO_VENDORS. This is necessary
5236: --only for the Releases because you can display the BPA header
5237: --attachments also with a release.
5238:
5244: l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
5245: FROM
5246: fnd_attached_docs_form_vl fad,
5247: fnd_documents_short_text fds
5248: WHERE ((entity_name=''PO_HEADERS'' AND
5249: pk1_value=to_char(phx.po_header_id))OR
5250: (entity_name = ''PO_RELEASES'' AND
5251: pk1_value = to_char(phx.po_release_id)) OR
5252: (entity_name = ''PO_VENDORS'' AND
5260: --
5261: l_head_url_attachment_query := 'CURSOR(
5262: SELECT fad.url web_page --Bug#4958642
5263: FROM fnd_attached_docs_form_vl fad
5264: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
5265: (fad.entity_name=''PO_RELEASES'' AND fad.pk1_value=to_char(phx.po_release_id)) OR
5266: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
5267: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
5268: )
5272:
5273: l_head_file_attachment_query := 'CURSOR(
5274: SELECT fad.file_name
5275: FROM fnd_attached_docs_form_vl fad
5276: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
5277: (fad.entity_name=''PO_RELEASES'' AND fad.pk1_value=to_char(phx.po_release_id)) OR
5278: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
5279: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
5280: )
5291: l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
5292: FROM
5293: fnd_attached_docs_form_vl fad,
5294: fnd_documents_short_text fds
5295: WHERE ((entity_name = ''PO_HEADERS'' AND
5296: pk1_value = to_char(phx.po_header_id)) OR
5297: (entity_name = ''PO_VENDORS'' AND
5298: pk1_value = to_char(phx.vendor_id)) OR
5299: (entity_name = ''PO_VENDOR_SITES'' AND
5305: --
5306: l_head_url_attachment_query := 'CURSOR(
5307: SELECT fad.url web_page --Bug#4958642
5308: FROM fnd_attached_docs_form_vl fad
5309: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
5310: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
5311: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
5312: )
5313: AND fad.datatype_id=5
5316:
5317: l_head_file_attachment_query := 'CURSOR(
5318: SELECT fad.file_name
5319: FROM fnd_attached_docs_form_vl fad
5320: WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
5321: (fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
5322: (fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
5323: )
5324: AND fad.datatype_id=6
5342: --Bug 4673653 - Added condition to show item level short text attachments
5343:
5344: /*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
5345: and CONTRACT 'S header- if there is a source document.
5346: Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
5347:
5348: l_line_short_attachment_query := ' CURSOR( SELECT plx.po_line_id , fds.short_text
5349: FROM
5350: fnd_attached_docs_form_vl fad,
5354: (fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
5355: fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548
5356: fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
5357: OR
5358: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
5359: AND plx.from_header_id IS NOT NULL)
5360: OR
5361: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
5362: AND plx.from_line_id IS NOT NULL)
5360: OR
5361: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
5362: AND plx.from_line_id IS NOT NULL)
5363: OR
5364: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.CONTRACT_ID)
5365: AND plx.CONTRACT_ID IS NOT NULL)
5366: ) AND
5367: function_name = ''PO_PRINTPO''
5368: AND fad.media_id = fds.media_id
5372: --Bug 4673653 - Use inventory org id instead of Org Id to get the item attachments
5373:
5374: /*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
5375: and CONTRACT 'S header- if there is a source document.
5376: Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
5377:
5378: l_line_url_attachment_query := 'CURSOR(
5379: SELECT fad.url web_page --Bug#4958642
5380: FROM fnd_attached_docs_form_vl fad
5379: SELECT fad.url web_page --Bug#4958642
5380: FROM fnd_attached_docs_form_vl fad
5381: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
5382: OR
5383: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
5384: AND plx.from_header_id IS NOT NULL)
5385: OR
5386: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
5387: AND plx.from_line_id IS NOT NULL)
5385: OR
5386: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
5387: AND plx.from_line_id IS NOT NULL)
5388: OR
5389: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.contract_id)
5390: AND plx.contract_id IS NOT NULL)
5391: OR
5392: (fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
5393: fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND
5399: --Bug 4673653 - Use inventory org id instead of Org Id to get the item attachments
5400:
5401: /*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
5402: and CONTRACT 'S header- if there is a source document.
5403: Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
5404:
5405: l_line_file_attachment_query := 'CURSOR(
5406: SELECT fad.file_name
5407: FROM fnd_attached_docs_form_vl fad
5406: SELECT fad.file_name
5407: FROM fnd_attached_docs_form_vl fad
5408: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
5409: OR
5410: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
5411: AND plx.from_header_id IS NOT NULL)
5412: OR
5413: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
5414: AND plx.from_line_id IS NOT NULL)
5412: OR
5413: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
5414: AND plx.from_line_id IS NOT NULL)
5415: OR
5416: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.contract_id)
5417: AND plx.contract_id IS NOT NULL)
5418: OR
5419: (fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
5420: fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND
5799: || 'AND pax1.parent_adjustment_id is null order by pax1.pricing_group_sequence) AS PRICE_MODIFIERS';
5800: l_price_modifier_query2 := 'CURSOR (SELECT pax2.* FROM PO_PRICE_ADJUSTMENTS_XML pax2 '
5801: || 'WHERE pax2.po_header_id = plx.from_header_id AND pax2.po_line_id = plx.from_line_id '
5802: || 'AND pax2.parent_adjustment_id is null order by pax2.pricing_group_sequence) AS ADD_PRICE_MODIFIERS';
5803: l_price_modifier_query3 := 'CURSOR (SELECT pha.segment1 ponum FROM po_headers_all pha '
5804: || 'WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM';
5805: --
5806:
5807: l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type,
5811: PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
5812: fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile,
5813: PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '
5814: || l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||'
5815: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5816:
5817: IF(p_document_subtype <> 'CONTRACTS') THEN -- contracts will have only headers
5818:
5819: SELECT count(*) into l_count FROM po_lines_all WHERE po_header_id = p_document_id;
5840: WHERE plx.po_header_id = phx.po_header_id and not exists
5841: (select ''x'' from po_lines_archive_all plaa where
5842: plaa.po_line_id = plx.po_line_id and
5843: plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
5844: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5845:
5846: END IF;
5847:
5848: SELECT count(*) into l_count FROM po_line_locations_all WHERE po_header_id = p_document_id;
5875: WHERE plx.po_header_id = phx.po_header_id and not exists
5876: (select ''x'' from po_lines_archive_all plaa where
5877: plaa.po_line_id = plx.po_line_id and
5878: plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
5879: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5880: END IF;
5881:
5882: IF(p_document_subtype <> 'BLANKET') THEN -- blankets will not have distributions
5883: SELECT count(*) into l_count FROM po_distributions_all WHERE po_header_id = p_document_id;
5903: FROM po_lines_xml plx WHERE plx.po_header_id = phx.po_header_id and not exists
5904: (select ''x'' from po_lines_archive_all plaa where
5905: plaa.po_line_id = plx.po_line_id and
5906: plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
5907: FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
5908: phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5909:
5910: END IF;
5911: END IF;
5926: l_price_modifier_query2 := 'CURSOR (SELECT pax2.* FROM PO_PRICE_ADJS_ARCHIVE_XML pax2 '
5927: || 'WHERE pax2.po_header_id = plx.from_header_id AND pax2.po_line_id = plx.from_line_id '
5928: || 'AND pax2.revision_num= PO_COMMUNICATION_PVT.getRevisionNum() AND '
5929: || 'pax2.parent_adjustment_id is null order by pax2.pricing_group_sequence) AS ADD_PRICE_MODIFIERS';
5930: l_price_modifier_query3 := 'CURSOR (SELECT pha.segment1 ponum FROM po_headers_archive_all pha '
5931: || 'WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM';
5932: --
5933:
5934: /* Bug#3574748: Added the condition SHIPMENT_TYPE in ('BLANKET','STANDARD') in shipment query. */
5943: PO_COMMUNICATION_PVT.getDocumentName() document_name,
5944: PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
5945: fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
5946: l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||'
5947: FROM PO_HEADERS_ARCHIVE_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5948:
5949: IF(p_document_subtype <> 'CONTRACTS') THEN -- contracts will have only headers
5950:
5951: SELECT count(*) into l_count FROM po_lines_archive_all WHERE po_header_id = p_document_id;
5968: '|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3
5969: || ',' || 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
5970: AND plx.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
5971: 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
5972: FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt
5973: WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId()
5974: AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
5975:
5976: END IF;
6002: 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 || '
6003: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
6004: AND plx.REVISION_NUM = (SELECT /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
6005: 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
6006: 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()';
6007: END IF;
6008:
6009: IF(p_document_subtype <> 'BLANKET') THEN -- blankets will not have distributions
6010: SELECT count(*) into l_count FROM po_distributions_archive_all WHERE po_header_id = p_document_id;
6032: 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 || '
6033: FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
6034: AND plx.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
6035: 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
6036: FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
6037: phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
6038:
6039: END IF; -- end of
6040: END IF; -- end of balnket if condition
6147: and ''Y'' = decode(nvl(pllaa.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllaa.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) )
6148: and plx.po_header_id = phx.po_header_id
6149: AND plx.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
6150: and pla.revision_num <= (select max(pb.revision_num)
6151: from po_headers_archive_all pb, po_releases_archive_all pr
6152: where pb.po_header_id = pr.po_header_id
6153: and pr.po_release_id = pcgt.po_release_id
6154: and pr.revision_num= pcgt.revision_number
6155: and pb.approved_date <= pr.approved_date
6167: /* for header long text */
6168: --bug#3768142 also added the condition to check if the document
6169: --type is a release so that even the release header documents
6170: --are retrieved. An order by is used so that first the
6171: --PO_HEADERS(BPA) attachments are printed followed by PO_RELEASES
6172: --attachments and then finally PO_VENDORS. This is necessary
6173: --only for the Releases because you can display the BPA header
6174: --attachments also with a release.
6175: --bug#3823799: Replaced the hard coded p_document id with PO_COMMUNICATION_PVT.getDocumentId() function
6187: fnd_attached_docs_form_vl fad,
6188: fnd_documents_long_text fdl
6189: WHERE ( (entity_name=''PO_RELEASES'' AND
6190: pk1_value= to_char(PO_COMMUNICATION_PVT.getDocumentId()) ) OR
6191: (entity_name = ''PO_HEADERS'' AND
6192: pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
6193: (entity_name = ''PO_VENDORS'' AND
6194: pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
6195: function_name = ''PO_PRINTPO''
6209: l_headerAttachmentsQuery := 'select fdl.long_text long_text
6210: FROM
6211: fnd_attached_docs_form_vl fad,
6212: fnd_documents_long_text fdl
6213: WHERE ((entity_name = ''PO_HEADERS'' AND
6214: pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
6215: (entity_name = ''PO_VENDORS'' AND
6216: pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
6217: function_name = ''PO_PRINTPO''
6232: Replacing fdl.long_text with get_clob(fds.rowid) */
6233:
6234: /*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
6235: and CONTRACT 'S header- if there is a source document.
6236: Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
6237:
6238: /*l_lineAttachQuery :='SELECT fds.long_text text, plx.po_line_id id
6239: FROM
6240: fnd_attached_docs_form_vl fad,
6241: fnd_documents_long_text fds,
6242: po_lines_all plx
6243: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
6244: OR
6245: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
6246: AND plx.from_header_id IS NOT NULL)
6247: OR
6248: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
6249: AND plx.from_line_id IS NOT NULL)
6247: OR
6248: (fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
6249: AND plx.from_line_id IS NOT NULL)
6250: OR
6251: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.CONTRACT_ID)
6252: AND plx.CONTRACT_ID IS NOT NULL)
6253: OR
6254: (fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
6255: fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548
6297: FND_DOCUMENTS D ,
6298: FND_ATTACHMENT_FUNCTIONS AF,
6299: fnd_doc_category_usages DCU,
6300: po_lines_all plx
6301: WHERE ( ad.entity_name=''PO_HEADERS''
6302: AND ad.pk1_value=to_char ( plx.from_header_id )
6303: AND plx.from_header_id IS NOT NULL )
6304: AND DCU.category_id = D.category_id
6305: AND DCU.attachment_function_id = AF.attachment_function_id
6341: FND_DOCUMENTS D ,
6342: FND_ATTACHMENT_FUNCTIONS AF,
6343: fnd_doc_category_usages DCU,
6344: po_lines_all plx
6345: WHERE ( ad.entity_name=''PO_HEADERS''
6346: AND ad.pk1_value=to_char ( plx.CONTRACT_ID )
6347: AND plx.CONTRACT_ID IS NOT NULL )
6348: AND DCU.category_id = D.category_id
6349: AND DCU.attachment_function_id = AF.attachment_function_id
7004: --toreturn the values in global variables
7005: --po_communication_pvt.g_town_or_city
7006: --po_communication_pvt.g_postal_code
7007: --and po_communication_pvt.g_state_or_province.
7008: --These functions are called by the PO_HEADERS_CHANGE_PRINT
7009: --report
7010:
7011: function getTownOrCity return varchar2 is
7012: begin
7230: Select ph.QUOTE_VENDOR_QUOTE_NUMBER, ph.SEGMENT1, ph.GLOBAL_AGREEMENT_FLAG into
7231: PO_COMMUNICATION_PVT.g_quote_number, PO_COMMUNICATION_PVT.g_agreement_number,
7232: PO_COMMUNICATION_PVT.g_agreement_flag
7233: FROM
7234: po_headers_all ph
7235: WHERE
7236: ph.PO_HEADER_ID = p_header_id;
7237:
7238:
7290: PO_COMMUNICATION_PVT.g_arcBuyer_title, PO_COMMUNICATION_PVT.g_arcAgent_id
7291:
7292: FROM
7293: PER_ALL_PEOPLE_F HRE,
7294: PO_HEADERS_ARCHIVE_ALL PHA,
7295: HR_LOOKUPS HRL
7296: WHERE HRL.LOOKUP_CODE(+) = HRE.TITLE AND
7297: HRL.LOOKUP_TYPE(+) = 'TITLE' AND
7298: HRE.PERSON_ID = PHA.AGENT_ID AND
7482: document and signature */
7483:
7484: select acceptance_required_flag
7485: into l_acceptance_req_flag
7486: from po_headers_all
7487: where po_header_id= p_header_id;
7488:
7489: IF (nvl(l_acceptance_req_flag,'N') <> 'S') THEN
7490: l_signed := TRUE;
7574: -- Query for getting the PO number i.e segment1.
7575: IF p_document_type in ('PO', 'PA') THEN
7576: IF (p_draft_id = -1 or p_is_conf_doc = 'Y') THEN --CLM
7577: SELECT ph.segment1 into l_po_number
7578: FROM po_headers_all ph
7579: WHERE po_header_id = p_document_id ;
7580: ELSE --CLM
7581: SELECT modification_number into l_po_number
7582: FROM po_drafts
7585: ELSE
7586: /* Begin Edit By Akyanama Bug # 13342437*/
7587: SELECT ph.segment1, release_num into l_po_number, l_release_num
7588: /* End Edit By Akyanama Bug # 13342437*/
7589: FROM po_headers_all ph, po_releases_all pr
7590: WHERE ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id ;
7591: END IF;
7592:
7593: EXCEPTION
8096: SELECT NVL(poh.pending_signature_flag, 'N')
8097: , hou.name
8098: INTO x_pendingSignatureFlag
8099: , PO_COMMUNICATION_PVT.g_ou_name
8100: -- FROM po_headers_all poh
8101: FROM po_headers_merge_v poh
8102: , hr_all_organization_units hou
8103: WHERE poh.po_header_id = p_documentID
8104: AND poh.draft_id = p_draftId --CLM
8097: , hou.name
8098: INTO x_pendingSignatureFlag
8099: , PO_COMMUNICATION_PVT.g_ou_name
8100: -- FROM po_headers_all poh
8101: FROM po_headers_merge_v poh
8102: , hr_all_organization_units hou
8103: WHERE poh.po_header_id = p_documentID
8104: AND poh.draft_id = p_draftId --CLM
8105: AND hou.organization_id = poh.org_id;
8157:
8158: d_progress NUMBER;
8159: d_module VARCHAR2(70) := 'po.plsql.PO_COMMUNICATION_PVT.setIsComplexWorkPO';
8160: l_is_complex BOOLEAN;
8161: l_style_id PO_HEADERS_ALL.style_id%TYPE;
8162:
8163:
8164: BEGIN
8165:
8188: d_progress := 20;
8189:
8190: SELECT poha.style_id
8191: INTO l_style_id
8192: FROM po_headers_archive_all poha
8193: WHERE poha.po_header_id = p_document_id
8194: AND poha.revision_num = p_revision_num;
8195:
8196: d_progress := 25;
8273: p_document_type VARCHAR2,
8274: p_profile_option VARCHAR2) RETURN VARCHAR2
8275: IS
8276:
8277: x_item_type po_headers_all.wf_item_type%TYPE;
8278: x_item_key po_headers_all.wf_item_key%TYPE;
8279: l_profile_value fnd_profile_option_values.profile_option_value%TYPE;
8280: l_progress VARCHAR2(10);
8281:
8274: p_profile_option VARCHAR2) RETURN VARCHAR2
8275: IS
8276:
8277: x_item_type po_headers_all.wf_item_type%TYPE;
8278: x_item_key po_headers_all.wf_item_key%TYPE;
8279: l_profile_value fnd_profile_option_values.profile_option_value%TYPE;
8280: l_progress VARCHAR2(10);
8281:
8282: l_preparer_user_id NUMBER;
8305:
8306: ELSE
8307: SELECT wf_item_type, wf_item_key
8308: INTO x_item_type, x_item_key
8309: FROM po_headers_all
8310: WHERE po_header_id = p_document_id;
8311:
8312: l_progress := '002';
8313: