[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,
309: , pli.ip_category_id ip_category_id
310: , pli.supplier_part_auxid supplier_part_auxid
311: , pli.catalog_name catalog_name
312: --
313: FROM po_headers_interface phi,
314: po_lines_interface pli,
315: po_distributions_interface pdi
316: WHERE phi.interface_header_id = pli.interface_header_id
317: AND pli.interface_line_id = pdi.interface_line_id
341: g_sourcing_errorcode number;
342: g_interface_source_code varchar2(25);
343: --
344:
345: g_mode po_headers_interface.action%type := null;
346: g_group_code po_headers_interface.group_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;
342: g_interface_source_code varchar2(25);
343: --
344:
345: g_mode po_headers_interface.action%type := null;
346: g_group_code po_headers_interface.group_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;
343: --
344:
345: g_mode po_headers_interface.action%type := null;
346: g_group_code po_headers_interface.group_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; --
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
508:
509: --
510:
511: FUNCTION validate_interface_records (
512: p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE
513: ) RETURN BOOLEAN;
514:
515: FUNCTION validate_config_id (
516: p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE
512: p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE
513: ) RETURN BOOLEAN;
514:
515: FUNCTION validate_config_id (
516: p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE
517: ) RETURN BOOLEAN;
518:
519: --
520:
552: p_document_creation_method IN VARCHAR2, --
553: p_orig_org_id IN NUMBER DEFAULT NULL, --
554: p_group_shipments IN VARCHAR2 DEFAULT NULL --
555: ) IS
556: x_document_number po_headers.segment1%type;
557: x_errorcode number;
558: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
559: l_api_name VARCHAR2(30) := 'create_documents(wrapper1)'; --< Bug 3210331 >
560: BEGIN
591: --
592:
593: /*============================================================================
594: Name: CREATE_DOCUMENTS
595: DESC: Create/Add to document from requisition data in the PO_HEADERS,LINES
596: and DISTRIBUTION interface tables.
597: ARGS: IN : x_batch_id IN number
598: --
599: IN: p_sourcing_k_doc_type IN VARCHAR2 - The document type that Sourcing
867: --
868: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
869:
870: -- CLM Autocreate
871: l_style_id PO_HEADERS_INTERFACE.style_id%TYPE;
872: l_clm_enabled_flag VARCHAR2(1):= 'N';
873: l_change_process_type PO_DOC_STYLE_HEADERS.change_process_type%TYPE;
874: l_is_clm_flow VARCHAR2(1):= 'N';
875:
896:
897: /* For now a batch has only one header, so get the document header id */
898: SELECT min(phi.interface_header_id)
899: INTO x_interface_header_id
900: FROM po_headers_interface phi
901: WHERE phi.batch_id = p_batch_id;
902:
903: /* Getting the style_id of the target document */
904: SELECT phi.style_id
902:
903: /* Getting the style_id of the target document */
904: SELECT phi.style_id
905: INTO l_style_id
906: FROM po_headers_interface phi
907: WHERE phi.interface_header_id=x_interface_header_id;
908:
909: l_progress := '015';
910: IF g_debug_stmt THEN
1044:
1045: /* For now a batch has only one header, so get the document header id */
1046: SELECT min(interface_header_id)
1047: INTO x_interface_header_id
1048: FROM po_headers_interface
1049: WHERE batch_id = p_batch_id; --
1050:
1051: l_progress:='040';
1052: --
1054: --
1055: --Modified the select statement to select document_subtype as well.
1056: SELECT document_type_code,document_subtype,nvl(interface_source_code,'NOCODE')
1057: INTO g_document_type,g_document_subtype,g_interface_source_code
1058: FROM po_headers_interface
1059: WHERE interface_header_id = x_interface_header_id;
1060:
1061: --
1062: -- tax is calculated for this document type
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;
1393: /*===========================================================================*/
1394:
1395: /* ============================================================================
1396: NAME: CREATE_PO
1397: DESC: Create/Add to PO from requisition data in the PO_HEADERS,LINES
1398: and DISTRIBUTION interface tables.
1399: ARGS: IN : x_interface_header_id IN number
1400: --
1401: IN: p_sourcing_k_doc_type IN VARCHAR2 - The document type that Sourcing
1424:
1425: X_line_found varchar2(1) := 'N';
1426: x_release_num number;
1427:
1428: x_document_num po_headers.segment1%type:=null; -- Bug 1093645
1429:
1430: l_api_name CONSTANT VARCHAR2(30) := 'create_po';
1431:
1432: x_org_id number;
1430: l_api_name CONSTANT VARCHAR2(30) := 'create_po';
1431:
1432: x_org_id number;
1433: /*bug # 2997337 */
1434: x_valid_ship_to po_headers.ship_to_location_id%TYPE;
1435: x_valid_bill_to po_headers.bill_to_location_id%TYPE;
1436: x_is_valid VARCHAR(1) := 'N';
1437: /*bug # 2997337 */
1438:
1431:
1432: x_org_id number;
1433: /*bug # 2997337 */
1434: x_valid_ship_to po_headers.ship_to_location_id%TYPE;
1435: x_valid_bill_to po_headers.bill_to_location_id%TYPE;
1436: x_is_valid VARCHAR(1) := 'N';
1437: /*bug # 2997337 */
1438:
1439: --
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;
1454: --
1455: l_document_creation_method po_headers_all.document_creation_method%type := p_document_creation_method; --
1456: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
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;
1455: l_document_creation_method po_headers_all.document_creation_method%type := p_document_creation_method; --
1456: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
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;
1456: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
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;
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;
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;
1466:
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;
1466:
1467: l_is_complex_work_po BOOLEAN := FALSE; --
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;
1466:
1467: l_is_complex_work_po BOOLEAN := FALSE; --
1468: l_style_id NUMBER; --
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:
1507:
1508: --
1509: SELECT phi.style_id
1510: INTO l_style_id
1511: FROM po_headers_interface phi
1512: WHERE phi.interface_header_id = x_interface_header_id;
1513:
1514: IF (l_style_id IS NOT NULL) THEN
1515: l_is_complex_work_po := PO_COMPLEX_WORK_PVT.is_complex_work_style(
1784: SELECT 'N'
1785: INTO l_keep_summary
1786: FROM dual
1787: WHERE exists (SELECT 'approved document'
1788: FROM po_headers
1789: WHERE po_header_id = interface.po_header_id
1790: AND NVL(approved_flag, 'N') IN ('R', 'Y'));
1791: EXCEPTION
1792: WHEN NO_DATA_FOUND THEN
1869: ** revision on the PO by one.
1870: */
1871: SELECT max(revision_num)
1872: INTO x_max_revision_num
1873: FROM po_headers_archive_all poha --
1875:
1876: /* Bug 493106 ecso 9/24/97
1877: * Revised date should be updated
1876: /* Bug 493106 ecso 9/24/97
1877: * Revised date should be updated
1878: * the same time revision is incremented.
1879: * Database field revised_date is defined as varchar(25).
1880: * Use same format as in po_headers_pkg2.check_new_revision
1881: x_new_revised_date := to_char(interface.last_update_date,
1882: 'DD-MON-YY HH24:MI');
1883: */
1884:
1885: l_progress:='110';
1886: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1887: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1888: p_token => l_progress,
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 --
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'),
1924: l_progress:='120';
1925: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1926: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1927: p_token => l_progress,
1928: p_message => 'Update PO_Headers Doc subtype is Release');
1929: END IF;
1930:
1931: UPDATE po_headers_all --
1932: SET closed_code = 'OPEN',
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 ,
2045: end if;
2046:
2047: --
2048: --The following insert is modified to take care of defaulting for sourcing
2049: --For more comments please refer to update po_headers_interface phi
2050: --in setup_interface_tables procedure.
2051: /* Bug 2816396
2052: Use the interface table value for pay_on_code when inserting into po_headers.pay_on_code
2053: */
2048: --The following insert is modified to take care of defaulting for sourcing
2049: --For more comments please refer to update po_headers_interface phi
2050: --in setup_interface_tables procedure.
2051: /* Bug 2816396
2052: Use the interface table value for pay_on_code when inserting into po_headers.pay_on_code
2053: */
2054:
2055:
2056: /*bug #2997337
2054:
2055:
2056: /*bug #2997337
2057: validating the ship_to and bill_to locations to check whether they are
2058: active or inactive before inserting into the PO_HEADERS table. If any of them
2059: is found inactive then a null value is inserted in the table in the respective
2060: column*/
2061:
2062: if (g_interface_source_code = 'SOURCING') then
2137: l_progress:= '210';
2138: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2139: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
2140: p_token => l_progress,
2141: p_message => 'Before Insert into PO_headers');
2142: END IF;
2143:
2144: INSERT INTO po_headers_all --
2145: (po_header_id,
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,
2305: l_progress:= '220';
2306: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
2307: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
2308: p_token => l_progress,
2309: p_message => 'After Insert into PO_headers');
2310: END IF;
2311:
2312: /* Bug#13542989: Start : Get Default document transimmision information and
2313: Calling PO_VENDOR_SITES_SV.get_transmission_defaults only when vendor_id and
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;
2348:
2349:
2350: --Bug#5024876: Removed the dangling 'END IF' left by Bug#4886821
2351:
2352: -- After insert into po_headers, insert a row into org_assignments for a global agreement
2353: IF nvl(interface.global_agreement_flag, 'N') = 'Y' then
2354: l_progress:= '240';
2355: select org_id
2356: into x_org_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.
2399: null,
2400: null,
2401: null,
2402: null,
2403: 'PO_HEADERS',
2404: interface.po_header_id,
2405: null,
2406: null,
2407: null,
2419: interface.auction_header_id,
2420: interface.auction_line_number,
2421: interface.bid_number,
2422: interface.bid_line_number,
2423: 'PO_HEADERS',
2424: interface.po_header_id,
2425: interface.created_by,
2426: interface.last_update_login ,
2427: null,
2435: interface.auction_header_id,
2436: NULL,
2437: interface.bid_number,
2438: NULL,
2439: 'PO_HEADERS',
2440: interface.po_header_id,
2441: interface.created_by,
2442: interface.last_update_login ,
2443: null,
2643: /* Bug 1183082
2644: If emergency po number is mentioned then the interface document number
2645: will have that value and it can be alphanumeric though the po num code
2646: is automatic.
2647: In case of automatic, we populate a dummy value in segment1(in po_headers)
2648: which is negative of po_header_id.
2649: We should be populating the segment1 from unique identifier control
2650: table only if the segment1 is negative of po_header id and
2651: hence the following logic.
2658: -- bug5174177
2659: -- Call Centralized API to get the next po number
2660: x_document_num :=
2661: PO_CORE_SV1.default_po_unique_identifier
2662: ( p_table_name => 'PO_HEADERS',
2663: p_org_id => g_purchasing_ou_id
2664: );
2665:
2666: IF g_debug_stmt THEN
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);
3133: l_routing_name RCV_ROUTING_HEADERS.routing_name%TYPE; --
3134:
3135: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
3136: l_manual_price_change_flag po_lines_all.manual_price_change_flag%TYPE := NULL; --bug 3495772
3137: l_from_type_lookup_code PO_HEADERS.type_lookup_code%type;--bug#3612701
3138:
3139: --
3140: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3141: x_secondary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
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
7973:
7974:
7975: /* ============================================================================
7976: NAME: CREATE_RFQ
7977: DESC: Create/Add to RFQ from requisition data in the PO_HEADERS,LINES
7978: and DISTRIBUTION interface tables.
7979: ARGS: IN : x_interface_header_id IN number
7980: ALGR:
7981:
7983: PROCEDURE create_rfq(x_interface_header_id IN number,
7984: x_document_id IN OUT NOCOPY number) IS
7985:
7986: x_quotation_class_code varchar2(25);
7987: x_document_num po_headers.segment1%type:=null; -- Bug 1093645
7988:
7989: l_api_name CONSTANT VARCHAR2(30) := 'create_documents';
7990:
7991: --
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'
8076: and document_subtype = interface.quote_type_lookup_code;
8077:
8078: l_progress := '060';
8079: /** Bug 881882, bgu, Apr. 29, 1999
8080: * For inserting record into po_headers view, it used
8081: * to_date(interface.h_rate_date, 'DD/MM/YYYY') for column rate_date.
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.
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,
8221:
8222: -- bug5174177
8223: x_document_num :=
8224: PO_CORE_SV1.default_po_unique_identifier
8225: ( p_table_name => 'PO_HEADERS_RFQ',
8226: p_org_id => g_purchasing_ou_id
8227: );
8228:
8229: IF g_debug_stmt THEN
8232: p_message => 'new po num = ' || x_document_num);
8233: END IF;
8234:
8235: l_progress := '130';
8236: UPDATE po_headers set segment1=x_document_num,
8237: clm_document_number =x_document_num
8238: where po_header_id=x_document_id;
8239:
8240: END IF;
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;
8515: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
8516: l_deliver_to_person_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_person_id%TYPE;
8517: l_line_type_id PO_LINES_INTERFACE.line_type_id%TYPE;
8518: l_vendor_id PO_VENDORS.vendor_id%TYPE;
8519: l_agent_id PO_HEADERS.agent_id%TYPE;
8520: l_expenditure_organization_id PO_DISTRIBUTIONS_INTERFACE.expenditure_organization_id%TYPE;
8521: l_project_id PO_DISTRIBUTIONS_INTERFACE.project_id%TYPE;
8522: l_task_id PO_DISTRIBUTIONS_INTERFACE.task_id%TYPE;
8523: l_bom_resource_id PO_DISTRIBUTIONS_INTERFACE.bom_resource_id%TYPE;
8552: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
8553: l_amount PO_LINES_INTERFACE.amount%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8559: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
8560: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
8553: l_amount PO_LINES_INTERFACE.amount%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8559: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
8560: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
8561: l_rate_for_req_fields PO_HEADERS_INTERFACE.rate%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8559: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
8560: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
8561: l_rate_for_req_fields PO_HEADERS_INTERFACE.rate%TYPE;
8562: l_po_func_unit_price PO_LINES_ALL.unit_price%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8559: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
8560: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
8561: l_rate_for_req_fields PO_HEADERS_INTERFACE.rate%TYPE;
8562: l_po_func_unit_price PO_LINES_ALL.unit_price%TYPE;
8563: -- Bug 3463242 END
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8559: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
8560: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
8561: l_rate_for_req_fields PO_HEADERS_INTERFACE.rate%TYPE;
8562: l_po_func_unit_price PO_LINES_ALL.unit_price%TYPE;
8563: -- Bug 3463242 END
8564:
8565: l_item_in_linv_pou VARCHAR2(1):= 'Y'; -- Bug 3433867
8612: pdi.rate
8613: -- Bug 3463242 END
8614: FROM PO_DISTRIBUTIONS_INTERFACE pdi,
8615: PO_LINES_INTERFACE pli,
8616: PO_HEADERS_INTERFACE phi,
8617: PO_REQUISITION_LINES_ALL prl, --
8618: MTL_PARAMETERS mp,
8619: HR_ORGANIZATION_INFORMATION hoi,
8620: PO_LINE_TYPES_B plt -- Bug 3463242
9231: )
9232: IS
9233:
9234: x_po_header_id number := null;
9235: x_document_num po_headers.segment1%type:=null;
9236: x_min_interface_line_id number:= null;
9237: x_order_type_lookup_code varchar2(25) := null;
9238: x_quotation_class_code varchar2(25) := null;
9239: x_count_dist number;
9246: x_vendor_product_num varchar2(240) := null;
9247: x_purchasing_uom varchar2(240) := null;
9248: x_pay_on_code varchar2(25) := null;
9249: x_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
9250: x_old_document_num po_headers.segment1%type:=null; -- Bug 700513, lpo, 07/15/98
9251:
9252: x_employee_id number;
9253: x_employee_name varchar2(240);
9254: x_requestor_location_id number;
9262: phi.vendor_id,
9263: phi.vendor_site_id,
9264: pdi.destination_organization_id
9265: from po_lines_interface pli,
9266: po_headers_interface phi,
9267: po_distributions_interface pdi
9268: where phi.interface_header_id = x_interface_header_id
9269: and phi.interface_header_id = pli.interface_header_id
9270: and pdi.interface_distribution_id =
9293: phi.rate_date,
9294: phi.document_subtype,
9295: pli.unit_price --
9296: FROM po_lines_interface pli,
9297: po_headers_interface phi,
9298: po_line_types plt
9299: WHERE phi.interface_header_id = x_interface_header_id
9300: AND phi.interface_header_id = pli.interface_header_id
9301: AND pli.requisition_line_id is null
9330: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
9331: PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
9332: END IF;
9333:
9334: UPDATE po_headers_interface
9335: SET process_code = 'IN PROCESS'
9336: WHERE interface_header_id = x_interface_header_id;
9337:
9338: l_progress := '010';
9352: min(document_num)
9353: INTO g_mode,
9354: g_group_code,
9355: x_old_document_num
9356: FROM po_headers_interface
9357: WHERE interface_header_id = x_interface_header_id;
9358:
9359: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
9360: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
9372: */
9373:
9374: IF (g_document_type = 'RFQ') THEN
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 --
9380: WHERE 'RFQ' = ph.type_lookup_code
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;
9383: WHERE interface_header_id = x_interface_header_id;
9384:
9385: ELSE
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 --
9391: WHERE decode(phi.document_subtype,
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
9413: /* Bug 482648 add currecny_code */
9414:
9415: /* Also get the pay_on_code from the document */
9416:
9417: UPDATE po_headers_interface phi
9418: SET (
9419: rate,
9420: rate_type_code,
9421: rate_date,
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 >
9449: p_token => l_progress,
9450: p_message => 'In std/planned/rfq');
9451: END IF;
9452:
9453: SELECT po_headers_s.nextval
9454: INTO x_po_header_id
9455: FROM sys.dual;
9456:
9457:
9470:
9471: /* If we are using automatic numbering, get segment1
9472: ** from the po_unique_identifier_control table.
9473: ** If we are using manual numbering, segment1
9474: ** should already be loaded into the po_headers_interface table.
9475: ** The checks to verify that a manual po number is unique
9476: ** is done on the client side.
9477: */
9478: /* ecso 4/23/98 emergency requisition enhancement
9478: /* ecso 4/23/98 emergency requisition enhancement
9479: ** For emergency requisitions, there is a reserved po num
9480: ** even though the document has not been created.
9481: ** Add the case where the document is NEW
9482: ** and there exists a document_num on po_headers_interface
9483: */
9484: --
9485: --modified the if condition to include PA
9486: IF (params.po_num_code='AUTOMATIC') AND
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'
9550: INTO x_po_header_id,
9551: x_pay_on_code,
9552: l_shipping_control --
9553: FROM po_headers_all ph, --
9554: po_headers_interface phi
9555: WHERE phi.interface_header_id = x_interface_header_id
9556: AND ph.segment1 = phi.document_num
9557: AND ph.type_lookup_code='BLANKET'
9558: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
9569:
9570: SELECT min(interface_line_id)
9571: INTO x_min_interface_line_id
9572: FROM po_lines_interface pli,
9573: po_headers_interface phi
9574: WHERE phi.interface_header_id=pli.interface_header_id
9575: AND phi.interface_header_id = x_interface_header_id;
9576:
9577: l_progress:='100';
9579: --modify the following update to default the values for the blanket also.
9580: --track 'PA' for the changes. Also please note that we have added deocode
9581: --for all the terms and conditions.Existing autocreate would not values
9582: --for different terms n condition columns and shipto billto columns in the
9583: --interface tables. But po_headers_interface table would contain values
9584: --for these columns when called from sourcing. So we are not defaulting
9585: --these values from params here but would do this in create_po procedure
9586: --in the order of interface,vendor,params.
9587: /* Bug 2816396
9592: p_token => l_progress,
9593: p_message => 'Sourcing to FPH-6 starts');
9594: END IF;
9595:
9596: UPDATE po_headers_interface phi
9597: SET (po_header_id,
9598: last_update_date,
9599: last_updated_by,
9600: document_num,
9674: --phi.rate_date,
9675: nvl(phi.rate_date,decode(g_interface_source_code,'SOURCING',decode(phi.rate_type_code,'User',sysdate),phi.rate_date)),
9676: --
9677: phi.rate,
9678: -- bug 8802204: Checking the value of the acceptance_required_flag from po_headers_interface
9679: decode(g_document_type, 'PO',
9680: nvl(phi.acceptance_required_flag,nvl(params.acceptance_required_flag,'N')), /* Bug 7518967 : Default Acceptance Required Check ER: Geting default acceptance_required_flag */
9681: 'PA',nvl(phi.acceptance_required_flag,nvl(params.acceptance_required_flag,'N')),
9682: params.acceptance_required_flag),
9689: 'PA',nvl(phi.min_release_amount,params.min_rel_amount),
9690: null),
9691: phi.document_subtype,
9692: l_shipping_control --
9693: FROM po_headers_interface phi2,
9694: po_lines_interface pli
9695: WHERE phi2.interface_header_id = phi.interface_header_id
9696: AND pli.interface_header_id=phi2.interface_header_id
9697: AND pli.interface_line_id = x_min_interface_line_id)
9710: IF (g_document_subtype = 'RELEASE') THEN
9711:
9712: select po_header_id
9713: into x_po_header_id
9714: from po_headers_interface
9715: where interface_header_id = x_interface_header_id;
9716:
9717: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
9718: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
9854: -- end of 1548597
9855: prl.drop_ship_flag, --
9856: prl.vmi_flag -- VMI FPH
9857: FROM po_lines_interface pli,
9858: po_headers_interface phi,
9859: po_requisition_lines_all prl, --
9860: po_line_types plt,
9861: po_lines_all pol --
9862: WHERE pli.interface_line_id = pli2.interface_line_id
9897: x_po_header_id,
9898: nvl(pli.negotiated_by_preparer_flag,'N'),
9899: nvl(pli.firm_flag,'N')
9900: FROM po_lines_interface pli,
9901: po_headers_interface phi
9902: WHERE pli.interface_line_id = pli2.interface_line_id
9903: AND pli.interface_header_id = phi.interface_header_id
9904: AND phi.interface_header_id = x_interface_header_id);*/
9905:
10058: nvl(pli.contractor_last_name, prl.candidate_last_name)
10059: --
10060: ,pol.supplier_part_auxid --13876074
10061: FROM po_lines_interface pli,
10062: po_headers_interface phi,
10063: po_requisition_lines_all prl, --
10064: po_line_types plt
10065: ,po_lines_all pol --13876074
10066: WHERE pli.interface_line_id = pli2.interface_line_id
10208: prd.oke_contract_deliverable_id)
10209: FROM po_requisition_lines_all prl, --
10210: po_req_distributions_all prd, --
10211: po_lines_interface pli,
10212: po_headers_interface phi
10213: WHERE prd.requisition_line_id = prl.requisition_line_id
10214: AND prl.requisition_line_id = pli.requisition_line_id -- Bug:1563888
10215: AND pli.interface_header_id = phi.interface_header_id
10216: AND phi.interface_header_id = x_interface_header_id;
10468: DELETE po_lines_interface
10469: WHERE interface_header_id = x_interface_header_id;
10470:
10471:
10472: DELETE po_headers_interface
10473: WHERE interface_header_id = x_interface_header_id;
10474:
10475: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
10476: PO_DEBUG.debug_end(p_log_head => g_log_head||'wrapup');
10653: INTO x_document_num,
10654: x_document_type_code,
10655: x_document_subtype,
10656: x_release_num
10657: FROM po_headers_interface phi
10658: WHERE phi.interface_header_id = x_interface_header_id;
10659:
10660: IF (g_document_type = 'RFQ') THEN
10661: x_document_subtype := 'RFQ';
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)
12836:
12837: SELECT por.po_release_id
12838: INTO g_po_release_id
12839: FROM po_releases_all por, --
12840: po_headers_interface phi
12841: WHERE phi.interface_header_id = x_interface_header_id
12842: AND phi.release_num = por.release_num
12843: AND phi.po_header_id = por.po_header_id
12844: AND NVL(por.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
12877: );
12878:
12879: SELECT POL.po_line_id
12880: INTO l_po_line_id
12881: FROM po_headers_interface PHI,
12882: po_lines_all POL
12883: WHERE PHI.interface_header_id = x_interface_header_id
12884: AND PHI.po_header_id = POL.po_header_id
12885: AND POL.line_num = x_po_line_num;
13018: 'INVENTORY', 'Y',
13019: 'SHOP FLOOR','Y')
13020: INTO x_receipt_required_flag
13021: FROM po_lines_interface pli,
13022: po_headers_interface phi,
13023: po_requisition_lines_all prl, --
13024: mtl_system_items msi,
13025: po_line_types plt,
13026: po_vendors pov,
13088: PO_LINE_TYPES PLT ,
13089: PO_SYSTEM_PARAMETERS_ALL PSP , --
13090: FINANCIALS_SYSTEM_PARAMS_ALL FSP, --
13091: PO_VENDORS POV,
13092: PO_HEADERS_INTERFACE PHI
13093: WHERE PLI.LINE_NUM = x_po_line_num
13094: AND PLI.shipment_num is not null
13095: AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
13096: AND NVL(fsp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
13209: PO_LINE_TYPES PLT ,
13210: PO_SYSTEM_PARAMETERS_ALL PSP , --
13211: FINANCIALS_SYSTEM_PARAMS_ALL FSP, --
13212: PO_VENDORS POV,
13213: PO_HEADERS_INTERFACE PHI
13214: WHERE PLI.LINE_NUM = x_po_line_num
13215: AND PLI.shipment_num is not null
13216: AND PLI.item_id = MSI.inventory_item_id
13217: AND MSI.organization_id=
13300: l_progress:='180';
13301: SELECT document_subtype
13302: into x_check_doc_sub_type
13303: from
13304: PO_HEADERS_INTERFACE
13305: WHERE
13306: INTERFACE_HEADER_ID=x_interface_header_id;
13307: exception
13308: When others then
13337: PO_LINE_TYPES PLT ,
13338: PO_SYSTEM_PARAMETERS_ALL PSP , --
13339: FINANCIALS_SYSTEM_PARAMS_ALL FSP, --
13340: PO_VENDORS POV,
13341: PO_HEADERS_INTERFACE PHI
13342: WHERE PLI.LINE_NUM = x_po_line_num
13343: AND PLI.shipment_num is not null
13344: AND NVL(psp.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
13345: AND NVL(fsp.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
14480: --End of Comments
14481: ----------------------------------------------------------------------------
14482:
14483: FUNCTION validate_interface_records (
14484: p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE
14485: ) RETURN BOOLEAN IS
14486:
14487: l_api_name CONSTANT VARCHAR2(30) := 'validate_interface_records';
14488: l_pass_validations BOOLEAN;
14518: --End of Comments
14519: ----------------------------------------------------------------------------
14520:
14521: FUNCTION validate_config_id (
14522: p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE
14523: ) RETURN BOOLEAN IS
14524:
14525: l_api_name CONSTANT VARCHAR2(30) := 'validate_config_id';
14526: l_num_config_id_lines NUMBER;
14810: l_po_ou_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
14811: l_inverse_rate_display_flag VARCHAR2(1) := 'N';
14812: l_display_rate NUMBER;
14813: l_progress VARCHAR2(3) := '000';
14814: l_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
14815: l_api_name CONSTANT VARCHAR2(30) := 'get_rate_for_req_price'; --< Bug 3210331 >
14816:
14817: BEGIN
14818: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >