[Home] [Help]
40: (vendor_id number := null,
41: ship_to_location_id number := null,
42: bill_to_location_id number := null,
43: -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
44: -- been modified to point to PO_HEADERS_ALL type.
45: ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%type := null,
46: fob_lookup_code PO_HEADERS_ALL.fob_lookup_code%type := null,
47: pay_on_code varchar2(25) := null,
48: freight_terms_lookup_code PO_HEADERS_ALL.freight_terms_lookup_code%type := null,
41: ship_to_location_id number := null,
42: bill_to_location_id number := null,
43: -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
44: -- been modified to point to PO_HEADERS_ALL type.
45: ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%type := null,
46: fob_lookup_code PO_HEADERS_ALL.fob_lookup_code%type := null,
47: pay_on_code varchar2(25) := null,
48: freight_terms_lookup_code PO_HEADERS_ALL.freight_terms_lookup_code%type := null,
49: terms_id number := null,
42: bill_to_location_id number := null,
43: -- Bug# 4546121:All columns that referred to the obsolete columns in po_vendors have
44: -- been modified to point to PO_HEADERS_ALL type.
45: ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%type := null,
46: fob_lookup_code PO_HEADERS_ALL.fob_lookup_code%type := null,
47: pay_on_code varchar2(25) := null,
48: freight_terms_lookup_code PO_HEADERS_ALL.freight_terms_lookup_code%type := null,
49: terms_id number := null,
50: type_1099 po_vendors.type_1099%type := null,
44: -- been modified to point to PO_HEADERS_ALL type.
45: ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%type := null,
46: fob_lookup_code PO_HEADERS_ALL.fob_lookup_code%type := null,
47: pay_on_code varchar2(25) := null,
48: freight_terms_lookup_code PO_HEADERS_ALL.freight_terms_lookup_code%type := null,
49: terms_id number := null,
50: type_1099 po_vendors.type_1099%type := null,
51: hold_flag po_vendors.hold_flag%type := null,
52: invoice_currency_code po_vendors.invoice_currency_code%type := null,
347: g_document_subtype po_headers_interface.document_subtype%type := null;
348: g_po_release_id number := null;
349: g_document_type varchar2(25) := null;
350: g_number_records_processed number;
351: g_purchasing_ou_id PO_HEADERS_ALL.org_id%TYPE; --
353: g_rate_for_req_fields NUMBER; --
354: g_line_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE; --
355:
348: g_po_release_id number := null;
349: g_document_type varchar2(25) := null;
350: g_number_records_processed number;
351: g_purchasing_ou_id PO_HEADERS_ALL.org_id%TYPE; --
352: g_hdr_requesting_ou_id PO_HEADERS_ALL.org_id%TYPE; --
354: g_line_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE; --
355:
356: /* Global variable to hold number of req lines
1228: ELSE
1229: l_progress:= '140';
1230: select segment1
1231: into x_document_number
1232: from po_headers_all --
1234:
1235: END IF;
1236: /* CONSIGNED FPI end */
1273: -- Need to populate document number for autocreate success message
1274: IF g_document_subtype = 'STANDARD' THEN
1275: select segment1
1276: into x_document_number
1277: from po_headers_all
1278: where po_header_id = x_document_id;
1279: END IF;
1280: --
1281: else
1302:
1303: IF g_document_subtype = 'STANDARD' THEN
1304:
1305: l_progress:= '160';
1306: update po_headers_all
1307: set authorization_status = 'APPROVED',
1308: approved_date = sysdate,
1309: approved_flag = 'Y'
1310: where po_header_id = x_document_id;
1451: l_contracts_call_exception Exception;
1452: --For using %type, dependence on OKC tables. Consider for refactor
1453: l_contract_doc_type VARCHAR2(150);
1454: --
1455: l_document_creation_method po_headers_all.document_creation_method%type := p_document_creation_method; --
1457:
1458: l_terms_id PO_HEADERS.terms_id%TYPE;
1459: l_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
1457:
1458: l_terms_id PO_HEADERS.terms_id%TYPE;
1459: l_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
1460: l_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
1461: l_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
1462: l_vs_terms_id PO_HEADERS.terms_id%TYPE;
1463: l_vs_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
1464: l_vs_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
1465: l_vs_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
1461: l_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
1462: l_vs_terms_id PO_HEADERS.terms_id%TYPE;
1463: l_vs_fob_lookup_code PO_HEADERS.fob_lookup_code%TYPE;
1464: l_vs_freight_lookup_code PO_HEADERS.freight_terms_lookup_code%TYPE;
1465: l_vs_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
1466:
1467: l_is_complex_work_po BOOLEAN := FALSE; --
1468: l_style_id NUMBER; --
1469: tax_document_id NUMBER;
1481: --
1482: l_default_method VARCHAR2(30);
1483: l_email_address PO_VENDOR_SITES_ALL.email_address%TYPE;
1484: l_fax_number VARCHAR2(100);
1485: l_document_num PO_HEADERS_ALL.segment1%TYPE;
1486: l_vendor_id PO_VENDOR_SITES_ALL.vendor_id%TYPE;
1487: l_vendor_site_id PO_VENDOR_SITES_ALL.vendor_site_id%TYPE;
1488: --
1489:
1889: p_message => 'Update PO_Headers Doc subtype is Std or planned');
1890: END IF;
1891: --Bug 2295672 jbalakri : added 'REJECTED' case in decode statement.
1892:
1893: UPDATE po_headers_all --
1895: decode(nvl(approved_flag,'N'),'N','N','F','F','R'),
1896: authorization_status =
1897: decode(nvl(authorization_status,'INCOMPLETE'),
1927: p_token => l_progress,
1928: p_message => 'Update PO_Headers Doc subtype is Release');
1929: END IF;
1930:
1931: UPDATE po_headers_all --
1933: closed_date = NULL,
1934: last_update_date = interface.last_update_date,
1935: last_updated_by = interface.last_updated_by ,
2140: p_token => l_progress,
2141: p_message => 'Before Insert into PO_headers');
2142: END IF;
2143:
2144: INSERT INTO po_headers_all --
2146: last_update_date,
2147: last_updated_by,
2148: segment1,
2314: vendor_site_id is present on PO to avoid no_data_found exception. */
2315:
2316: SELECT vendor_id, vendor_site_id
2317: INTO l_vendor_id, l_vendor_site_id
2318: FROM po_headers_all
2319: WHERE po_header_id = INTERFACE.po_header_id;
2320: IF l_vendor_id IS NOT NULL AND
2321: l_vendor_site_id IS NOT NULL THEN
2322:
2330: x_fax_number => l_fax_number,
2331: x_document_num => l_document_num
2332: );
2333:
2334: update po_headers_all
2335: set SUPPLIER_NOTIF_METHOD = nvl(l_default_method,'NONE'),
2336: EMAIL_ADDRESS = decode(l_default_method, 'EMAIL', l_email_address, null),
2337: FAX = decode(l_default_method, 'FAX', l_fax_number, null)
2338: where po_header_id = interface.po_header_id;
2353: IF nvl(interface.global_agreement_flag, 'N') = 'Y' then
2354: l_progress:= '240';
2355: select org_id
2356: into x_org_id
2357: from po_headers_all
2358: where po_header_id = interface.po_header_id;
2359:
2360: --
2361: --Replaced the INSERT statement with call to Row Handler.
2670: END IF;
2671:
2672: l_progress := '325';
2673:
2674: UPDATE po_headers_all --
2676: clm_document_number = x_document_num
2677: where po_header_id=x_document_id;
2678:
3114: l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
3115: l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
3116: --
3117: l_negotiated_by_preparer_flag po_lines_all.negotiated_by_preparer_flag%type; --
3118: l_type_lookup_code po_headers_all.type_lookup_code%type; --
3120:
3121: -- oneoff 3201308 start
3122: l_needby_prf varchar2(1);
3115: l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
3116: --
3117: l_negotiated_by_preparer_flag po_lines_all.negotiated_by_preparer_flag%type; --
3118: l_type_lookup_code po_headers_all.type_lookup_code%type; --
3119: l_global_agreement_flag po_headers_all.global_agreement_flag%type; --
3121: -- oneoff 3201308 start
3122: l_needby_prf varchar2(1);
3123: l_shipto_prf varchar2(1);
3465: --bug#3612701 modified the sql to fetch type lookup code
3466: --of the source document as well.
3467: SELECT pol.unit_meas_lookup_code,poh.type_lookup_code
3468: INTO l_ga_uom,l_from_type_lookup_code
3469: FROM po_lines_all pol,po_headers_all poh
3470: WHERE pol.po_line_id = interface.from_line_id
3471: and poh.po_header_id=interface.from_header_id
3472: and poh.po_header_id=pol.po_header_id;
3473: --bug#3612701
4220: l_negotiated_by_preparer_flag := 'Y';
4221: ELSIF interface.from_header_id is not null THEN
4222: l_progress := '390';
4223: SELECT type_lookup_code,global_agreement_flag into l_type_lookup_code,l_global_agreement_flag
4224: FROM po_headers_all
4225: WHERE po_header_id=interface.from_header_id;
4226: -- if the source document is global agreement.
4227: IF l_type_lookup_code='BLANKET' and l_global_agreement_flag='Y' THEN
4228: l_progress := '395';
5751: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
5752: l_manual_price_change_flag po_line_locations_all.manual_price_change_flag%TYPE := NULL; --bug 3495772
5753:
5754: -- Bug 5208159
5755: l_from_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5756:
5757: --
5758: x_shipment_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
5759: x_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
5824: BEGIN
5825:
5826: SELECT poh.type_lookup_code
5827: INTO l_from_type_lookup_code
5828: FROM po_headers_all poh
5829: WHERE poh.po_header_id=interface.from_header_id ;
5830:
5831: IF g_debug_stmt THEN
5832: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
5872: l_progress := '020';
5873: select unit_meas_lookup_code
5874: into x_po_uom
5875: from po_lines_all pol , --
5876: po_headers_all poh --
5878: and pol.po_header_id = interface.po_header_id
5879: and pol.line_num = interface.line_num;
5880: -- Bug 2735840 START
7079: -- Bug 7661419 end
7080: --
7081: --
7082: l_uom_conversion_rate MTL_UOM_CONVERSIONS.conversion_rate%TYPE := 1;
7083: l_currency_conversion_rate PO_HEADERS_ALL.rate%TYPE := 1;
7084: --
7085: --
7086:
7087: /* Bug 1030123: cursor to get all the distributions based on the line id */
7105:
7106: l_amount_ordered NUMBER; --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
7107: l_drop_ship_flag po_line_locations.drop_ship_flag%type; --bug#3603067
7108: -- bug 5208159
7109: l_from_type_lookup_code po_headers_all.TYPE_LOOKUP_CODE%type;
7110:
7111: --introduced to hold the value of drop_ship_flag for shipments
7112: BEGIN
7113: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
7277: BEGIN
7278:
7279: SELECT poh.type_lookup_code
7280: INTO l_from_type_lookup_code
7281: FROM po_headers_all poh
7282: WHERE poh.po_header_id=interface.from_header_id ;
7283:
7284: EXCEPTION
7285: WHEN OTHERS THEN
7307: l_progress := '090';
7308: select unit_meas_lookup_code
7309: into x_po_uom
7310: from po_lines_all pol , --
7311: po_headers_all poh --
7313: and pol.po_header_id = interface.po_header_id
7314: and pol.line_num = interface.line_num;
7315: -- Bug 2735840 START
8059:
8060: IF (g_mode = 'ADD') THEN
8061:
8062: l_progress := '040';
8063: UPDATE po_headers_all --
8065: last_updated_by = interface.last_updated_by,
8066: last_update_login = interface.last_update_login,
8067: status_lookup_code = 'I'
8082: * But since the data type of interface.h_rate_date is already date,
8083: * this is unneccssary and causing problem when system date mask is
8084: * defined otherwise.
8085: */
8086: INSERT INTO po_headers_all --
8088: last_update_date,
8089: last_updated_by,
8090: segment1,
8505: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
8506: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
8507: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
8508: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
8509: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
8510:
8511: l_item_id PO_LINES_INTERFACE.item_id%TYPE;
8512: l_category_id PO_LINES_INTERFACE.category_id%TYPE;
8513: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
9375: l_progress:='030';
9376: UPDATE po_headers_interface phi
9377: SET po_header_id =
9378: (SELECT ph.po_header_id
9379: FROM po_headers_all ph --
9381: AND phi.document_num = ph.segment1
9382: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99)) --
9383: WHERE interface_header_id = x_interface_header_id;
9386: l_progress:='040';
9387: UPDATE po_headers_interface phi
9388: SET po_header_id =
9389: (SELECT ph.po_header_id
9390: FROM po_headers_all ph --
9392: 'RELEASE','BLANKET',
9393: phi.document_subtype) = ph.type_lookup_code
9394: AND phi.document_num = ph.segment1
9424: rate,
9425: rate_type,
9426: rate_date,
9427: currency_code
9428: FROM po_headers_all ph --
9430: WHERE interface_header_id = x_interface_header_id;
9431:
9432: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
9549: ph.shipping_control --
9550: INTO x_po_header_id,
9551: x_pay_on_code,
9552: l_shipping_control --
9553: FROM po_headers_all ph, --
9555: WHERE phi.interface_header_id = x_interface_header_id
9556: AND ph.segment1 = phi.document_num
9557: AND ph.type_lookup_code='BLANKET'
10959: END IF;
10960:
10961: SELECT NVL(max(pl.line_num),0)
10962: INTO x_line_num
10963: FROM po_headers_all ph,
10964: po_lines_all pl
10965: WHERE pl.po_header_id = ph.po_header_id
10966: AND ph.segment1 = x_document_num
10967: AND ph.type_lookup_code =
11151: END IF;
11152:
11153: SELECT nvl(max(poll.shipment_num),0)
11154: INTO x_shipment_num
11155: FROM po_headers_all ph,
11156: po_line_locations_all poll,
11157: po_releases_all pr
11158: WHERE ph.po_header_id = poll.po_header_id
11159: AND ph.segment1 = x_document_num
11372: pol.line_num
11373: INTO x_po_line_id,
11374: x_po_line_num
11375: FROM po_lines_all pol,
11376: po_headers_all poh
11377: WHERE poh.segment1 = x_document_num
11378: AND pol.line_num = x_interface_line_num
11379: AND poh.type_lookup_code =
11380: DECODE(g_document_type, 'RFQ', g_document_type,x_document_subtype)
11506: , po_line_id
11507: INTO x_po_line_num
11508: , x_po_line_id
11509: FROM po_lines_all POL2
11510: , po_headers_all POH
11511: , po_line_types_b PLT --
11512: WHERE POH.segment1 = x_document_num
11513: AND POH.po_header_id = POL2.po_header_id
11514: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
11620: po_line_id
11621: INTO x_po_line_num,
11622: x_po_line_id
11623: FROM po_lines_all POL2,
11624: po_headers_all POH
11625: WHERE POH.segment1 = x_document_num
11626: AND POH.po_header_id = POL2.po_header_id
11627: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
11628: AND POH.type_lookup_code = 'RFQ'
11991: END IF;
11992:
11993: SELECT NVL(max(pl.line_num), 0)
11994: INTO x_line_num
11995: FROM po_headers_all ph,
11996: po_lines_all pl
11997: WHERE pl.po_header_id = ph.po_header_id
11998: AND ph.segment1 = x_document_num
11999: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99)
12224:
12225: SELECT pol.po_line_id
12226: INTO x_po_line_id
12227: FROM po_lines_all pol,
12228: po_headers_all poh,
12229: po_lines_interface pli
12230: WHERE pol.po_header_id = poh.po_header_id
12231: AND poh.segment1 = x_document_num
12232: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99)
13553:
13554: l_progress := '030';
13555: select nvl(max(line_num),0)
13556: into x_line_num
13557: from po_headers_all ph, --
13559: where pl.po_header_id = ph.po_header_id
13560: and ph.segment1 = x_document_num
13561: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
13614: l_progress := '080';
13615:
13616: select nvl(max(shipment_num),0)
13617: into x_shipment_num
13618: from po_headers_all ph, --
13620: po_releases_all pr --
13621: where ph.po_header_id = poll.po_header_id
13622: and ph.segment1 = x_document_num