[Home] [Help]
184:
185: l_supplier_username fnd_user.user_name%type;
186: l_planner_username fnd_user.user_name%type;
187: l_planner_disp_name varchar2(2000);
188: l_type_lookup_code po_headers_all.type_lookup_code%type;
189:
190: n_varname Wf_Engine.NameTabTyp;
191: n_varval Wf_Engine.NumTabTyp;
192: t_varname Wf_Engine.NameTabTyp;
332: x_progress := '003';
333:
334: select revision_num
335: into l_revision_num
336: from PO_HEADERS_ALL
337: where po_header_id = p_doc_id;
338:
339: ELSIF p_doc_type = 'RELEASE' THEN
340:
468: update po_releases_all
469: set change_requested_by = null
470: where po_release_id = p_release_id;
471: ELSE
472: update po_headers_all
473: set change_requested_by = null
474: where po_header_id = p_header_id;
475: END IF;
476:
514:
515:
516: --Commiting Status to "APPOVED".
517: if(p_can_rel_id is null) then
518: update po_headers_all
519: set authorization_status = 'APPROVED'
520: where po_header_id = p_can_hdr_id;
521: else
522: update po_releases_all
549: --Restoring PO's Authorization status back to "IN PROCESS".
550: if(p_can_rel_id is null) then
551: if(p_can_line_loc_id is null) then
552: select cancel_flag into l_cancel_flag
553: from po_headers_all
554: where po_header_id = p_can_hdr_id;
555: else
556: select cancel_flag into l_cancel_flag
557: from po_line_locations_all
556: select cancel_flag into l_cancel_flag
557: from po_line_locations_all
558: where line_location_id = p_can_line_loc_id;
559: end if;
560: update po_headers_all
561: set authorization_status = 'IN PROCESS'
562: where po_header_id = p_can_hdr_id;
563: else
564: if(p_can_line_loc_id is null) then
588: END IF;
589: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590: --Restoring PO's Authorization status back to "IN PROCESS".
591: if(p_can_rel_id is null) then
592: update po_headers_all
593: set authorization_status = 'IN PROCESS'
594: where po_header_id = p_can_hdr_id;
595: else
596: update po_releases_all
623: l_can_reason po_change_requests.request_reason%TYPE;
624: l_cancel_backing_req po_change_requests.cancel_backing_req%TYPE;
625: l_can_doc_type po_document_types_all.document_type_code%TYPE;
626: l_can_doc_subtype po_document_types_all.document_subtype%TYPE;
627: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
628: l_return_status varchar2(1);
629: x_progress varchar2(3);
630: l_ship_can_err_msg varchar2(2000) := fnd_message.get_string('PO','PO_CHN_CAN_SHIP_ERR');
631: l_hdr_can_err_msg varchar2(2000) := fnd_message.get_string('PO','PO_CHN_CAN_HDR_ERR');
680: end if;
681:
682: select type_lookup_code
683: into l_type_lookup_code
684: from po_headers_all
685: where po_header_id = l_can_hdr_id;
686: if(l_can_rel_id is null) then
687: l_can_doc_type := 'PO';
688: if(l_type_lookup_code = 'STANDARD') then
745: l_can_doc_type;
746: exit when l_cancel_hdr_csr%NOTFOUND;
747: select type_lookup_code
748: into l_type_lookup_code
749: from po_headers_all
750: where po_header_id = l_can_hdr_id;
751: if(l_can_rel_id is null) then
752: l_can_doc_type := 'PO';
753: if(l_type_lookup_code = 'STANDARD') then
821: l_Acceptance_id number;
822: -- Bug 2850566
823: l_rowid ROWID;
824: l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
825: l_acc_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
826: -- End of Bug 2850566
827: l_Last_Update_Date date;
828: l_Last_Updated_By number;
829: l_Creation_Date date := TRUNC(SYSDATE);
885: agent_id
886: into
887: l_revision_num,
888: l_employee_id
889: from po_headers
890: where po_header_id = l_document_id;
891: else
892: l_Po_Release_Id := l_document_id;
893:
952: -- End of Bug 2850566 RBAIRRAJ
953:
954: -- Reset the Acceptance required Flag
955: --Bug 6772960 - Start
956: --Update the last update date when po_headers_all/po_releases_all tables are updated.
957: if l_po_release_id is not null then
958: update po_releases
959: set acceptance_required_flag = 'N',
960: LAST_UPDATE_DATE = SYSDATE,
960: LAST_UPDATE_DATE = SYSDATE,
961: acceptance_due_date = ''
962: where po_release_id = l_po_release_id;
963: else
964: update po_headers
965: set acceptance_required_flag = 'N',
966: LAST_UPDATE_DATE = SYSDATE,
967: acceptance_due_date = ''
968: where po_header_id = l_po_header_id;
1039: select pov.vendor_name
1040: into l_vendor
1041: from
1042: po_vendors pov,
1043: po_headers poh
1044: where pov.vendor_id = poh.vendor_id
1045: and poh.po_header_id=l_document_id;
1046: else
1047: select pov.vendor_name
1047: select pov.vendor_name
1048: into l_vendor
1049: from
1050: po_releases por,
1051: po_headers_all poh, --
1053: where por.po_release_id = l_document_id
1054: and por.po_header_id = poh.po_header_id
1055: and poh.vendor_id = pov.vendor_id;
1156: l_api_name varchar2(50) := 'KickOffPOApproval';
1157: l_org_id number;
1158: l_agent_id number;
1159: l_document_id number;
1160: l_document_num po_headers_all.segment1%TYPE;
1161: l_document_type_code po_document_types_all.DOCUMENT_TYPE_CODE%TYPE;
1162: l_document_subtype po_document_types_all.DOCUMENT_SUBTYPE%TYPE;
1163: l_seq number;
1164: l_item_key po_headers_all.wf_item_key%TYPE;
1160: l_document_num po_headers_all.segment1%TYPE;
1161: l_document_type_code po_document_types_all.DOCUMENT_TYPE_CODE%TYPE;
1162: l_document_subtype po_document_types_all.DOCUMENT_SUBTYPE%TYPE;
1163: l_seq number;
1164: l_item_key po_headers_all.wf_item_key%TYPE;
1165: l_item_type po_headers_all.wf_item_type%TYPE;
1166: l_workflow_process PO_DOCUMENT_TYPES_V.WF_APPROVAL_PROCESS%TYPE;
1167:
1168: /* Bug 2810156 BEGIN*/
1161: l_document_type_code po_document_types_all.DOCUMENT_TYPE_CODE%TYPE;
1162: l_document_subtype po_document_types_all.DOCUMENT_SUBTYPE%TYPE;
1163: l_seq number;
1164: l_item_key po_headers_all.wf_item_key%TYPE;
1165: l_item_type po_headers_all.wf_item_type%TYPE;
1166: l_workflow_process PO_DOCUMENT_TYPES_V.WF_APPROVAL_PROCESS%TYPE;
1167:
1168: /* Bug 2810156 BEGIN*/
1169: l_orig_itemtype po_headers.wf_item_type%TYPE;
1165: l_item_type po_headers_all.wf_item_type%TYPE;
1166: l_workflow_process PO_DOCUMENT_TYPES_V.WF_APPROVAL_PROCESS%TYPE;
1167:
1168: /* Bug 2810156 BEGIN*/
1169: l_orig_itemtype po_headers.wf_item_type%TYPE;
1170: l_orig_itemkey po_headers.wf_item_key%TYPE;
1171: l_user_id number;
1172: l_resp_id number;
1173: l_appl_id number;
1166: l_workflow_process PO_DOCUMENT_TYPES_V.WF_APPROVAL_PROCESS%TYPE;
1167:
1168: /* Bug 2810156 BEGIN*/
1169: l_orig_itemtype po_headers.wf_item_type%TYPE;
1170: l_orig_itemkey po_headers.wf_item_key%TYPE;
1171: l_user_id number;
1172: l_resp_id number;
1173: l_appl_id number;
1174: /* Bug 2810156 END */
1189: l_org_id,
1190: l_document_num,
1191: l_agent_id
1192: from po_releases_all por,
1193: po_headers_all poh
1194: where po_release_id = p_release_id
1195: and por.po_header_id=poh.po_header_id;
1196:
1197: l_Document_Type_Code := 'RELEASE';
1207: l_document_num,
1208: l_agent_id,
1209: l_document_subtype,
1210: l_org_id
1211: from po_headers_all poh
1212: where
1213: poh.po_header_id = p_header_id;
1214:
1215: if l_Document_SubType in ('BLANKET', 'CONTRACT') then
1228: BEGIN
1229: if (NVL(l_Document_Type_Code, 'PO') <> 'RELEASE') then
1230: select wf_item_type, wf_item_key
1231: into l_orig_itemtype, l_orig_itemkey
1232: from po_headers_all
1233: where po_header_id = l_document_id;
1234: else
1235: select wf_item_type, wf_item_key
1236: into l_orig_itemtype, l_orig_itemkey
1363: x_progress := '001';
1364: if (l_document_type_code <> 'RELEASE') then
1365: select revision_num
1366: into l_revision_num
1367: from po_headers_all
1368: where po_header_id = l_document_id;
1369: IsPOHeaderRejected(
1370: 1.0,
1371: l_return_status,
1471:
1472: l_progress varchar2(5) := '000';
1473:
1474: l_rel_total number;
1475: l_rel_currency po_headers.currency_code%TYPE;
1476: --l_blanket_num number;
1477: l_blanket_num po_headers_all.segment1%TYPE;
1478: --l_release_num number;
1479: l_release_num po_releases_all.release_num%TYPE;
1473:
1474: l_rel_total number;
1475: l_rel_currency po_headers.currency_code%TYPE;
1476: --l_blanket_num number;
1477: l_blanket_num po_headers_all.segment1%TYPE;
1478: --l_release_num number;
1479: l_release_num po_releases_all.release_num%TYPE;
1480: l_po_doc_id number;
1481: l_rel_doc_id number;
1480: l_po_doc_id number;
1481: l_rel_doc_id number;
1482: l_acc_req_flag varchar2(1);
1483: l_document varchar2(32000);
1484: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
1485: l_document_type varchar2(2000);
1486: l_po_num po_headers_all.segment1%TYPE;
1487: l_revision_num number;
1488: l_po_total number;
1482: l_acc_req_flag varchar2(1);
1483: l_document varchar2(32000);
1484: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
1485: l_document_type varchar2(2000);
1486: l_po_num po_headers_all.segment1%TYPE;
1487: l_revision_num number;
1488: l_po_total number;
1489: l_po_currency po_headers.currency_code%TYPE;
1490: l_vendor_id number;
1485: l_document_type varchar2(2000);
1486: l_po_num po_headers_all.segment1%TYPE;
1487: l_revision_num number;
1488: l_po_total number;
1489: l_po_currency po_headers.currency_code%TYPE;
1490: l_vendor_id number;
1491: l_vendor_site_id number;
1492: l_supplier_name po_vendors.vendor_name%TYPE;
1493: l_sup_address_line1 po_vendor_sites_all.address_line1%TYPE;
1496: l_sup_city po_vendor_sites_all.city%TYPE;
1497: l_sup_state po_vendor_sites_all.state%TYPE;
1498: l_sup_zip po_vendor_sites_all.zip%TYPE;
1499: l_order_date varchar2(2000);
1500: l_fob po_headers_all.fob_lookup_code%TYPE;
1501: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
1502: l_ship_to_id number;
1503: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
1504: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
1497: l_sup_state po_vendor_sites_all.state%TYPE;
1498: l_sup_zip po_vendor_sites_all.zip%TYPE;
1499: l_order_date varchar2(2000);
1500: l_fob po_headers_all.fob_lookup_code%TYPE;
1501: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
1502: l_ship_to_id number;
1503: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
1504: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
1505: l_ship_addr_l3 hr_locations_all.address_line_3%TYPE;
1529: lc_ship_to_location hr_locations_all.location_code%TYPE;
1530: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
1531: lc_reason po_change_requests.request_reason%TYPE;
1532: lc_split varchar2(2000);
1533: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
1534: cursor l_po_chg_req_csr(grp_id number, l_requestor_id number) is
1535: select
1536: to_char(LINE_NUM),
1537: to_char(SHIPMENT_NUM),
1761: l_carrier,
1762: l_ship_to_id,
1763: l_type_lookup_code,
1764: l_global_agreement_flag
1765: from po_headers_all
1766: where po_header_id = l_header_id;
1767:
1768: if(l_type_lookup_code = 'STANDARD') then
1769: l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_STD_PO');
1807: l_carrier,
1808: l_ship_to_id,
1809: l_type_lookup_code
1810: from po_releases_all pr,
1811: po_headers_all ph
1812: where pr.po_release_id = l_release_id
1813: and pr.po_header_id = ph.po_header_id;
1814:
1815: if(l_type_lookup_code = 'BLANKET') then
2293:
2294: l_progress varchar2(5) := '000';
2295:
2296: l_rel_total number;
2297: l_rel_currency po_headers.currency_code%TYPE;
2298: --l_blanket_num number;
2299: l_blanket_num po_headers_all.segment1%TYPE;
2300: --l_release_num number;
2301: l_release_num po_releases_all.release_num%TYPE;
2295:
2296: l_rel_total number;
2297: l_rel_currency po_headers.currency_code%TYPE;
2298: --l_blanket_num number;
2299: l_blanket_num po_headers_all.segment1%TYPE;
2300: --l_release_num number;
2301: l_release_num po_releases_all.release_num%TYPE;
2302: l_po_doc_id number;
2303: l_rel_doc_id number;
2302: l_po_doc_id number;
2303: l_rel_doc_id number;
2304: l_acc_req_flag varchar2(1);
2305: l_document varchar2(32000);
2306: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
2307: l_document_type varchar2(2000);
2308: l_po_num po_headers_all.segment1%TYPE;
2309: l_revision_num number;
2310: l_po_total number;
2304: l_acc_req_flag varchar2(1);
2305: l_document varchar2(32000);
2306: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
2307: l_document_type varchar2(2000);
2308: l_po_num po_headers_all.segment1%TYPE;
2309: l_revision_num number;
2310: l_po_total number;
2311: l_po_currency po_headers.currency_code%TYPE;
2312: l_vendor_id number;
2307: l_document_type varchar2(2000);
2308: l_po_num po_headers_all.segment1%TYPE;
2309: l_revision_num number;
2310: l_po_total number;
2311: l_po_currency po_headers.currency_code%TYPE;
2312: l_vendor_id number;
2313: l_vendor_site_id number;
2314: l_supplier_name po_vendors.vendor_name%TYPE;
2315: l_sup_address_line1 po_vendor_sites_all.address_line1%TYPE;
2318: l_sup_city po_vendor_sites_all.city%TYPE;
2319: l_sup_state po_vendor_sites_all.state%TYPE;
2320: l_sup_zip po_vendor_sites_all.zip%TYPE;
2321: l_order_date varchar2(2000);
2322: l_fob po_headers_all.fob_lookup_code%TYPE;
2323: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
2324: l_ship_to_id number;
2325: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
2326: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
2319: l_sup_state po_vendor_sites_all.state%TYPE;
2320: l_sup_zip po_vendor_sites_all.zip%TYPE;
2321: l_order_date varchar2(2000);
2322: l_fob po_headers_all.fob_lookup_code%TYPE;
2323: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
2324: l_ship_to_id number;
2325: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
2326: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
2327: l_ship_addr_l3 hr_locations_all.address_line_3%TYPE;
2352: lc_ship_to_location hr_locations_all.location_code%TYPE;
2353: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
2354: lc_reason po_change_requests.request_reason%TYPE;
2355: lc_split varchar2(2000);
2356: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
2357:
2358: cursor l_po_chg_req_csr(grp_id number, l_planner_id number) is
2359: select
2360: distinct
2594: l_carrier,
2595: l_ship_to_id,
2596: l_type_lookup_code,
2597: l_global_agreement_flag
2598: from po_headers_all
2599: where po_header_id = l_header_id;
2600:
2601: if(l_type_lookup_code = 'STANDARD') then
2602: l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_STD_PO');
2640: l_carrier,
2641: l_ship_to_id,
2642: l_type_lookup_code
2643: from po_releases_all pr,
2644: po_headers_all ph
2645: where pr.po_release_id = l_release_id
2646: and pr.po_header_id = ph.po_header_id;
2647:
2648: if(l_type_lookup_code = 'BLANKET') then
3116: l_header_id number;
3117: l_release_id number;
3118: l_grp_id number;
3119: l_rel_total number;
3120: l_rel_currency po_headers.currency_code%TYPE;
3121: --l_blanket_num number;
3122: l_blanket_num po_headers_all.segment1%TYPE;
3123: --l_release_num number;
3124: l_release_num po_releases_all.release_num%TYPE;
3118: l_grp_id number;
3119: l_rel_total number;
3120: l_rel_currency po_headers.currency_code%TYPE;
3121: --l_blanket_num number;
3122: l_blanket_num po_headers_all.segment1%TYPE;
3123: --l_release_num number;
3124: l_release_num po_releases_all.release_num%TYPE;
3125: l_po_doc_id number;
3126: l_rel_doc_id number;
3125: l_po_doc_id number;
3126: l_rel_doc_id number;
3127: l_acc_req_flag varchar2(1);
3128: l_document varchar2(32000);
3129: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
3130: l_document_type varchar2(2000);
3131: l_po_num po_headers_all.segment1%TYPE;
3132: l_revision_num number;
3133: l_po_total number;
3127: l_acc_req_flag varchar2(1);
3128: l_document varchar2(32000);
3129: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
3130: l_document_type varchar2(2000);
3131: l_po_num po_headers_all.segment1%TYPE;
3132: l_revision_num number;
3133: l_po_total number;
3134: l_po_currency po_headers.currency_code%TYPE;
3135: l_vendor_id number;
3130: l_document_type varchar2(2000);
3131: l_po_num po_headers_all.segment1%TYPE;
3132: l_revision_num number;
3133: l_po_total number;
3134: l_po_currency po_headers.currency_code%TYPE;
3135: l_vendor_id number;
3136: l_vendor_site_id number;
3137: l_supplier_name po_vendors.vendor_name%TYPE;
3138: l_sup_address_line1 po_vendor_sites_all.address_line1%TYPE;
3141: l_sup_city po_vendor_sites_all.city%TYPE;
3142: l_sup_state po_vendor_sites_all.state%TYPE;
3143: l_sup_zip po_vendor_sites_all.zip%TYPE;
3144: l_order_date varchar2(2000);
3145: l_fob po_headers_all.fob_lookup_code%TYPE;
3146: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
3147: l_ship_to_id number;
3148: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
3149: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
3142: l_sup_state po_vendor_sites_all.state%TYPE;
3143: l_sup_zip po_vendor_sites_all.zip%TYPE;
3144: l_order_date varchar2(2000);
3145: l_fob po_headers_all.fob_lookup_code%TYPE;
3146: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
3147: l_ship_to_id number;
3148: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
3149: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
3150: l_ship_addr_l3 hr_locations_all.address_line_3%TYPE;
3176: lc_ship_to_location hr_locations_all.location_code%TYPE;
3177: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
3178: lc_reason po_change_requests.request_reason%TYPE;
3179: lc_split varchar2(2000);
3180: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE;
3181: l_po_rev number;
3182: l_rel_rev number;
3183:
3184: cursor l_po_chg_req_csr(grp_id number) is
3511: l_carrier,
3512: l_ship_to_id,
3513: l_type_lookup_code,
3514: l_global_agreement_flag
3515: from po_headers_all
3516: where po_header_id = l_header_id;
3517:
3518: if(l_type_lookup_code = 'STANDARD') then
3519: l_document_type := fnd_message.get_string('PO','PO_WF_NOTIF_STD_PO');
3563: l_carrier,
3564: l_ship_to_id,
3565: l_type_lookup_code
3566: from po_releases_all pr,
3567: po_headers_all ph
3568: where pr.po_release_id = l_release_id
3569: and pr.po_header_id = ph.po_header_id;
3570:
3571: if(l_type_lookup_code = 'BLANKET') then
4191: l_api_name varchar2(50):='GEN_NTF_FOR_SUP_BUY_RP';
4192: l_progress varchar2(5) := '000';
4193: l_po_release_id number;
4194: l_rel_total number;
4195: l_rel_currency po_headers.currency_code%TYPE;
4196: --l_blanket_num number;
4197: l_blanket_num po_headers_all.segment1%TYPE;
4198: --l_release_num number;
4199: l_release_num po_releases_all.release_num%TYPE;
4193: l_po_release_id number;
4194: l_rel_total number;
4195: l_rel_currency po_headers.currency_code%TYPE;
4196: --l_blanket_num number;
4197: l_blanket_num po_headers_all.segment1%TYPE;
4198: --l_release_num number;
4199: l_release_num po_releases_all.release_num%TYPE;
4200: l_document varchar2(32000);
4201: l_document_header_id number;
4199: l_release_num po_releases_all.release_num%TYPE;
4200: l_document varchar2(32000);
4201: l_document_header_id number;
4202: l_document_type varchar2(2000);
4203: l_po_num po_headers_all.segment1%TYPE;
4204: l_revision_num number;
4205: l_po_total number;
4206: l_po_currency po_headers.currency_code%TYPE;
4207: l_vendor_id number;
4202: l_document_type varchar2(2000);
4203: l_po_num po_headers_all.segment1%TYPE;
4204: l_revision_num number;
4205: l_po_total number;
4206: l_po_currency po_headers.currency_code%TYPE;
4207: l_vendor_id number;
4208: l_vendor_site_id number;
4209: l_supplier_name po_vendors.vendor_name%TYPE;
4210: l_sup_address_line1 po_vendor_sites_all.address_line1%TYPE;
4213: l_sup_city po_vendor_sites_all.city%TYPE;
4214: l_sup_state po_vendor_sites_all.state%TYPE;
4215: l_sup_zip po_vendor_sites_all.zip%TYPE;
4216: l_order_date varchar2(2000);
4217: l_fob po_headers_all.fob_lookup_code%TYPE;
4218: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
4219: l_ship_to_id number;
4220: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
4221: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
4214: l_sup_state po_vendor_sites_all.state%TYPE;
4215: l_sup_zip po_vendor_sites_all.zip%TYPE;
4216: l_order_date varchar2(2000);
4217: l_fob po_headers_all.fob_lookup_code%TYPE;
4218: l_carrier po_headers_all.ship_via_lookup_code%TYPE;
4219: l_ship_to_id number;
4220: l_ship_addr_l1 hr_locations_all.address_line_1%TYPE;
4221: l_ship_addr_l2 hr_locations_all.address_line_2%TYPE;
4222: l_ship_addr_l3 hr_locations_all.address_line_3%TYPE;
4227: l_base_url varchar2(2000);
4228: l_base_url_tag varchar2(2000);
4229:
4230: l_document1 VARCHAR2(32000) := '';
4231: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
4232: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
4233:
4234: l_old_prom_date varchar2(2000);
4235: l_new_prom_date varchar2(2000);
4228: l_base_url_tag varchar2(2000);
4229:
4230: l_document1 VARCHAR2(32000) := '';
4231: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
4232: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
4233:
4234: l_old_prom_date varchar2(2000);
4235: l_new_prom_date varchar2(2000);
4236:
4449: l_carrier,
4450: l_ship_to_id,
4451: l_type_lookup_code,
4452: l_global_agreement_flag
4453: from po_headers_all
4454: where po_header_id = l_document_header_id;
4455: else
4456: select ph.segment1,
4457: pr.release_num,
4480: l_ship_to_id,
4481: l_type_lookup_code,
4482: l_global_agreement_flag
4483: from po_releases_all pr,
4484: po_headers_all ph
4485: where pr.po_release_id = l_po_release_id
4486: and pr.po_header_id = ph.po_header_id;
4487: end if;
4488: l_progress := '002';
4961: document IN OUT nocopy VARCHAR2,
4962: document_type IN OUT nocopy VARCHAR2)
4963: IS
4964:
4965: l_po_num po_headers_all.segment1%TYPE;
4966: l_supplier_name po_vendors.vendor_name%TYPE;
4967: l_blanket_num po_headers_all.segment1%TYPE;
4968: --l_release_num number;
4969: l_release_num po_releases_all.release_num%TYPE;
4963: IS
4964:
4965: l_po_num po_headers_all.segment1%TYPE;
4966: l_supplier_name po_vendors.vendor_name%TYPE;
4967: l_blanket_num po_headers_all.segment1%TYPE;
4968: --l_release_num number;
4969: l_release_num po_releases_all.release_num%TYPE;
4970: l_document_header_info varchar2(2000);
4971: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
4967: l_blanket_num po_headers_all.segment1%TYPE;
4968: --l_release_num number;
4969: l_release_num po_releases_all.release_num%TYPE;
4970: l_document_header_info varchar2(2000);
4971: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
4972: l_global_agreement_flag varchar2(3);
4973: l_header_cancel varchar2(1);
4974: l_id NUMBER;
4975: l_revision_num NUMBER; -- RDP changes
5061: l_po_total,
5062: l_currency_code,
5063: l_display_name
5064: from
5065: po_headers_all pha,
5066: po_vendors pv,
5067: PO_ALL_DOC_STYLE_LINES PDSL
5068: where pha.po_header_id = l_header_id
5069: and pha.vendor_id = pv.vendor_id
5127: l_rel_total,
5128: l_rel_currency_code
5129: from
5130: po_releases_all pra,
5131: po_headers_all pha,
5132: po_vendors pv
5133: where pra.po_release_id = l_release_id
5134: and pra.po_header_id = pha.po_header_id
5135: and pha.vendor_id = pv.vendor_id;
5187: document IN OUT nocopy VARCHAR2,
5188: document_type IN OUT nocopy VARCHAR2)
5189: IS
5190:
5191: l_po_num po_headers_all.segment1%TYPE;
5192: l_blanket_num po_headers_all.segment1%TYPE;
5193: --l_release_num number;
5194: l_release_num po_releases_all.release_num%TYPE;
5195: l_buyer_name hr_all_organization_units_tl.name%TYPE;
5188: document_type IN OUT nocopy VARCHAR2)
5189: IS
5190:
5191: l_po_num po_headers_all.segment1%TYPE;
5192: l_blanket_num po_headers_all.segment1%TYPE;
5193: --l_release_num number;
5194: l_release_num po_releases_all.release_num%TYPE;
5195: l_buyer_name hr_all_organization_units_tl.name%TYPE;
5196: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5192: l_blanket_num po_headers_all.segment1%TYPE;
5193: --l_release_num number;
5194: l_release_num po_releases_all.release_num%TYPE;
5195: l_buyer_name hr_all_organization_units_tl.name%TYPE;
5196: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5197: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
5198: l_doc_hdr_info varchar2(2000);
5199:
5200: l_id number;
5193: --l_release_num number;
5194: l_release_num po_releases_all.release_num%TYPE;
5195: l_buyer_name hr_all_organization_units_tl.name%TYPE;
5196: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5197: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
5198: l_doc_hdr_info varchar2(2000);
5199:
5200: l_id number;
5201: l_header_cancel varchar2(1);
5263: l_buyer_name,
5264: l_type_lookup_code,
5265: l_global_agreement_flag,
5266: l_display_name
5267: from po_headers_all pha,
5268: hr_all_organization_units_tl hou,
5269: PO_ALL_DOC_STYLE_LINES PDSL
5270: where pha.po_header_id = l_header_id
5271: and pha.org_id = hou.organization_id(+)
5306: into l_blanket_num,
5307: l_release_num,
5308: l_buyer_name,
5309: l_type_lookup_code
5310: from po_headers_all pha,
5311: po_releases_all pra,
5312: hr_all_organization_units_tl hou
5313: where pra.po_release_id = l_release_id
5314: and pha.po_header_id = pra.po_header_id
5371: document IN OUT nocopy VARCHAR2,
5372: document_type IN OUT nocopy VARCHAR2)
5373: IS
5374:
5375: l_po_num po_headers_all.segment1%TYPE;
5376: l_blanket_num po_headers_all.segment1%TYPE;
5377: --l_release_num number;
5378: l_release_num po_releases_all.release_num%TYPE;
5379: l_supplier_name po_vendors.vendor_name%TYPE;
5372: document_type IN OUT nocopy VARCHAR2)
5373: IS
5374:
5375: l_po_num po_headers_all.segment1%TYPE;
5376: l_blanket_num po_headers_all.segment1%TYPE;
5377: --l_release_num number;
5378: l_release_num po_releases_all.release_num%TYPE;
5379: l_supplier_name po_vendors.vendor_name%TYPE;
5380: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5376: l_blanket_num po_headers_all.segment1%TYPE;
5377: --l_release_num number;
5378: l_release_num po_releases_all.release_num%TYPE;
5379: l_supplier_name po_vendors.vendor_name%TYPE;
5380: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5381: l_document_header_info varchar2(2000);
5382:
5383: l_id number;
5384: l_header_cancel varchar2(1);
5395: l_release_id NUMBER;
5396: l_chg_req_grp_id NUMBER;
5397:
5398: l_message_name fnd_new_messages.message_name%TYPE;
5399: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
5400: l_display_name varchar2(1000);
5401: l_msg_header varchar2(1000);
5402:
5403:
5431: l_po_num,
5432: l_supplier_name,
5433: l_global_agreement_flag,
5434: l_display_name
5435: from po_headers_all pha,
5436: po_vendors pv,
5437: PO_ALL_DOC_STYLE_LINES PDSL
5438: where pha.po_header_id = l_header_id
5439: and pv.vendor_id = pha.vendor_id
5471: into l_type_lookup_code,
5472: l_blanket_num,
5473: l_release_num,
5474: l_supplier_name
5475: from po_headers_all pha,
5476: po_releases_all pra,
5477: po_vendors pv
5478: where pra.po_release_id = l_release_id
5479: and pra.po_header_id = pha.po_header_id
5528: document IN OUT nocopy VARCHAR2,
5529: document_type IN OUT nocopy VARCHAR2)
5530: IS
5531:
5532: l_po_num po_headers_all.segment1%TYPE;
5533: l_blanket_num po_headers_all.segment1%TYPE;
5534: --l_release_num number;
5535: l_release_num po_releases_all.release_num%TYPE;
5536: l_supplier_name po_vendors.vendor_name%TYPE;
5529: document_type IN OUT nocopy VARCHAR2)
5530: IS
5531:
5532: l_po_num po_headers_all.segment1%TYPE;
5533: l_blanket_num po_headers_all.segment1%TYPE;
5534: --l_release_num number;
5535: l_release_num po_releases_all.release_num%TYPE;
5536: l_supplier_name po_vendors.vendor_name%TYPE;
5537: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5533: l_blanket_num po_headers_all.segment1%TYPE;
5534: --l_release_num number;
5535: l_release_num po_releases_all.release_num%TYPE;
5536: l_supplier_name po_vendors.vendor_name%TYPE;
5537: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5538: l_document_header_info varchar2(2000);
5539:
5540: l_id number;
5541: l_header_cancel varchar2(1);
5552: l_release_id NUMBER;
5553: l_chg_req_grp_id NUMBER;
5554:
5555: l_message_name fnd_new_messages.message_name%TYPE;
5556: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
5557: l_display_name varchar2(1000);
5558: l_msg_header varchar2(1000);
5559:
5560: BEGIN
5587: l_po_num,
5588: l_supplier_name,
5589: l_global_agreement_flag,
5590: l_display_name
5591: from po_headers_all pha,
5592: po_vendors pv,
5593: PO_ALL_DOC_STYLE_LINES PDSL
5594: where pha.po_header_id = l_header_id
5595: and pv.vendor_id = pha.vendor_id
5627: into l_type_lookup_code,
5628: l_blanket_num,
5629: l_release_num,
5630: l_supplier_name
5631: from po_headers_all pha,
5632: po_releases_all pra,
5633: po_vendors pv
5634: where pra.po_release_id = l_release_id
5635: and pra.po_header_id = pha.po_header_id
5706: itemkey => itemkey,
5707: aname => 'PO_HEADER_ID');
5708:
5709: select count(*) into l_retro_count
5710: from po_change_requests pcr, po_headers_all poh
5711: where poh.po_header_id = pcr.document_header_id
5712: and poh.po_header_id = l_po_header_id
5713: and poh.type_lookup_code = 'BLANKET'
5714: and pcr.change_request_group_id = l_chg_req_grp_id
5776: l_revision_num number;
5777: l_chg_req_grp_id number;
5778: l_add_changes_accepted number;
5779: l_responded_by number;
5780: l_authorization_status po_headers_all.authorization_status%TYPE;
5781: l_check_result varchar2(1);
5782:
5783: x_progress varchar2(3) := '000';
5784: BEGIN
5839: where po_release_id = l_release_id;
5840:
5841: else
5842:
5843: update po_headers_all set
5844: authorization_status = 'REQUIRES REAPPROVAL',
5845: revision_num = revision_num + 1,
5846: revised_date = sysdate,
5847: last_update_date = sysdate,
5864: set authorization_status = l_authorization_status,
5865: change_requested_by = null
5866: where po_release_id = l_release_id;
5867: else
5868: update po_headers_all
5869: set authorization_status = l_authorization_status,
5870: change_requested_by = null
5871: where po_header_id = l_header_id;
5872: end if;
5935: l_header_id := l_document_id;
5936: l_release_id := null;
5937: select revision_num
5938: into l_revision_num
5939: from po_headers_all
5940: where po_header_id = l_document_id;
5941: end if;
5942: x_progress := '002';
5943: PO_ACKNOWLEDGE_PO_GRP.carry_over_acknowledgement(1.0,
5983: where po_release_id = l_document_id;
5984: else
5985: select acceptance_required_flag
5986: into l_acc_flag
5987: from po_headers_all
5988: where po_header_id = l_document_id;
5989: end if;
5990: if(l_acc_flag= 'Y') then
5991: resultout := 'Y';
6013: IS
6014: l_document_id number;
6015: p_header_id number;
6016: l_document_type po_document_types_all.document_type_code%TYPE;
6017: l_authorization_status po_headers_all.authorization_status%TYPE;
6018: l_acceptance_required_flag po_headers_all.acceptance_required_flag%TYPE; -- fix for bug 4946410
6019: l_supplier_user_id number;
6020: l_change_request_group_id number;
6021: l_supplier_username fnd_user.user_name%TYPE;
6014: l_document_id number;
6015: p_header_id number;
6016: l_document_type po_document_types_all.document_type_code%TYPE;
6017: l_authorization_status po_headers_all.authorization_status%TYPE;
6018: l_acceptance_required_flag po_headers_all.acceptance_required_flag%TYPE; -- fix for bug 4946410
6019: l_supplier_user_id number;
6020: l_change_request_group_id number;
6021: l_supplier_username fnd_user.user_name%TYPE;
6022: l_blanket_num po_headers_all.segment1%TYPE;
6018: l_acceptance_required_flag po_headers_all.acceptance_required_flag%TYPE; -- fix for bug 4946410
6019: l_supplier_user_id number;
6020: l_change_request_group_id number;
6021: l_supplier_username fnd_user.user_name%TYPE;
6022: l_blanket_num po_headers_all.segment1%TYPE;
6023: --l_release_num number;
6024: l_release_num po_releases_all.release_num%TYPE;
6025: l_buyer_name hr_all_organization_units_tl.name%TYPE;
6026: l_po_num po_headers_all.segment1%TYPE;
6022: l_blanket_num po_headers_all.segment1%TYPE;
6023: --l_release_num number;
6024: l_release_num po_releases_all.release_num%TYPE;
6025: l_buyer_name hr_all_organization_units_tl.name%TYPE;
6026: l_po_num po_headers_all.segment1%TYPE;
6027: l_revision_num number;
6028: l_revision_num2 number;
6029: l_notification_subject varchar2(2000);
6030: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
6026: l_po_num po_headers_all.segment1%TYPE;
6027: l_revision_num number;
6028: l_revision_num2 number;
6029: l_notification_subject varchar2(2000);
6030: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
6031: l_doc_hdr_info varchar2(1000);
6032: l_buyer_agent_id number;
6033: l_responded_by_id number;
6034: l_buyer_username fnd_user.user_name%TYPE;
6064:
6065: x_progress varchar2(100) := '000';
6066: t_varname Wf_Engine.NameTabTyp;
6067: t_varval Wf_Engine.TextTabTyp;
6068: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
6069: l_po_header_id po_headers_all.po_header_id%TYPE;
6070: l_id number;
6071: l_header_cancel varchar2(1);
6072:
6065: x_progress varchar2(100) := '000';
6066: t_varname Wf_Engine.NameTabTyp;
6067: t_varval Wf_Engine.TextTabTyp;
6068: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
6069: l_po_header_id po_headers_all.po_header_id%TYPE;
6070: l_id number;
6071: l_header_cancel varchar2(1);
6072:
6073: cursor l_header_cancel_csr(grp_id number) is
6160: l_type_lookup_code,
6161: l_buyer_agent_id,
6162: l_vendor_id,
6163: l_acceptance_required_flag
6164: from po_headers_all pha,
6165: po_releases_all pra,
6166: hr_all_organization_units_tl hou
6167: where pra.po_release_id = l_document_id
6168: and pha.po_header_id = pra.po_header_id
6260: l_buyer_agent_id,
6261: l_global_agreement_flag,
6262: l_vendor_id,
6263: l_acceptance_required_flag
6264: from po_headers_all pha,
6265: hr_all_organization_units_tl hou
6266: where pha.po_header_id = l_document_id
6267: and pha.org_id = hou.organization_id(+)
6268: and hou.language(+) = userenv('LANG');
6268: and hou.language(+) = userenv('LANG');
6269:
6270: select revision_num
6271: into l_revision_num2
6272: from po_headers_archive_all
6273: where po_header_id=l_document_id
6274: and latest_external_flag='Y';
6275:
6276: l_header_cancel := 'N';
6308: l_notification_subject := fnd_message.get;
6309: end if;
6310:
6311: /*
6312: update po_headers_all
6313: set change_requested_by = null
6314: where po_header_id = l_document_id;
6315: */
6316:
6647: agent_id
6648: into
6649: l_doc_subtype,
6650: l_employee_id
6651: from po_headers_all
6652: where po_header_id = l_header_id;
6653:
6654: if(l_doc_subtype = 'BLANKET' or l_doc_subtype = 'CONTRACT') then
6655: l_doc_type := 'PA';
6664: pra.agent_id
6665: into
6666: l_doc_subtype,
6667: l_employee_id
6668: from po_headers_all pha, po_releases_all pra
6669: where pra.po_release_id = l_release_id
6670: and pra.po_header_id = pha.po_header_id;
6671: end if;
6672: x_progress := '001';
6841: l_seq number;
6842: l_supplier_username fnd_user.user_name%TYPE;
6843: l_notification_subject varchar2(2000);
6844:
6845: --l_po_num po_headers_all.segment1%TYPE;
6846: --l_blanket_num po_headers_all.segment1%TYPE;
6847: --l_release_num number;
6848: --l_buyer_name hr_all_organization_units_tl.name%TYPE;
6849: --l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
6842: l_supplier_username fnd_user.user_name%TYPE;
6843: l_notification_subject varchar2(2000);
6844:
6845: --l_po_num po_headers_all.segment1%TYPE;
6846: --l_blanket_num po_headers_all.segment1%TYPE;
6847: --l_release_num number;
6848: --l_buyer_name hr_all_organization_units_tl.name%TYPE;
6849: --l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
6850: --l_document_type varchar2(2000);
6845: --l_po_num po_headers_all.segment1%TYPE;
6846: --l_blanket_num po_headers_all.segment1%TYPE;
6847: --l_release_num number;
6848: --l_buyer_name hr_all_organization_units_tl.name%TYPE;
6849: --l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
6850: --l_document_type varchar2(2000);
6851: l_buyer_agent_id number;
6852: l_responded_by_id number;
6853: l_buyer_username fnd_user.user_name%TYPE;
6901: l_progress := '001';
6902: if(p_release_id is null) then
6903: select pha.agent_id
6904: into l_buyer_agent_id
6905: from po_headers_all pha
6906: where pha.po_header_id = p_header_id;
6907: else
6908: select pra.agent_id
6909: into l_buyer_agent_id
7153: l_kickoff_msg varchar2(2000);
7154: l_doc_id number;
7155: l_employee_id number;
7156:
7157: l_authorization_status po_headers_all.authorization_status%TYPE;
7158: l_add_changes_accepted NUMBER;
7159:
7160: l_doc_type po_document_types_all.document_type_code%TYPE;
7161: l_doc_subtype po_document_types_all.document_subtype%TYPE;
7162: l_id number;
7163: l_action po_lookup_codes.lookup_code%TYPE;
7164: l_acc_id number;
7165: l_rej_id number;
7166: l_closed_code po_headers_all.closed_code%type;
7167:
7168: cursor l_accept_csr(grp_id number) is
7169: select change_request_id
7170: from po_change_requests
7202: l_agent_id,
7203: l_doc_subtype,
7204: l_closed_code
7205: from po_releases_all por,
7206: po_headers_all pha
7207: where po_release_id = p_release_id
7208: and por.po_header_id= pha.po_header_id;
7209:
7210:
7219: l_agent_id,
7220: l_org_id,
7221: l_doc_subtype,
7222: l_closed_code
7223: from po_headers_all poh
7224: where poh.po_header_id = p_header_id ;
7225:
7226: if(l_doc_subtype = 'BLANKET' or l_doc_subtype = 'CONTRACT') then
7227: l_doc_type := 'PA';
7493: aname => 'CHANGE_REQUEST_GROUP_ID');
7494: if(l_release_id is null) then
7495: select agent_id
7496: into l_agent_id
7497: from po_headers_all
7498: where po_header_id = l_header_id;
7499: else
7500: select agent_id
7501: into l_agent_id
7633: cursor c_getReqHdrId(p_po_header_id_csr IN NUMBER) is
7634: select distinct porh.requisition_header_id
7635: from po_requisition_headers_all porh,
7636: po_requisition_lines_all porl,
7637: po_headers_all poh,
7638: po_line_locations_all poll
7639: where porh.requisition_header_id = porl.requisition_header_id AND
7640: porl.line_location_id = poll.line_location_id AND
7641: poh.po_header_id = poll.po_header_id AND
7769:
7770: if(p_release_id is null) then
7771: select agent_id
7772: into l_buyer_agent_id
7773: from po_headers_all
7774: where po_headers_all.po_header_id = p_header_id;
7775: else
7776: select pra.agent_id
7777: into l_buyer_agent_id
7770: if(p_release_id is null) then
7771: select agent_id
7772: into l_buyer_agent_id
7773: from po_headers_all
7774: where po_headers_all.po_header_id = p_header_id;
7775: else
7776: select pra.agent_id
7777: into l_buyer_agent_id
7778: from po_releases_all pra,
7775: else
7776: select pra.agent_id
7777: into l_buyer_agent_id
7778: from po_releases_all pra,
7779: po_headers_all pha
7780: where pra.po_release_id = p_release_id
7781: and pra.po_header_id = pha.po_header_id;
7782: end if;
7783:
8261: pcr.request_status,
8262: poh.type_lookup_code,
8263: pcr.document_num
8264: FROM PO_CHANGE_REQUESTS pcr,
8265: PO_HEADERS_ALL poh
8266: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
8267: AND pcr.REQUEST_LEVEL = 'HEADER'
8268: AND pcr.ACTION_TYPE = 'CANCELLATION'
8269: AND pcr.document_header_id = poh.po_header_id;
8274: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr;
8275:
8276: CURSOR getRevisionNumAck(po_header_id_csr IN NUMBER) IS
8277: SELECT revision_num
8278: FROM po_headers_all
8279: WHERE po_header_id = po_header_id_csr;
8280:
8281: CURSOR getRevisionNumAckRel(po_header_id_csr IN NUMBER,po_release_id_csr IN NUMBER) IS
8282: SELECT pora.revision_num