ORACLE PURCHASING TABLES
segment1 - is the
system–assigned number you use to identify in forms and reports.
Table Name Columns
PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID,
PREPARER_ID, SEGMENT1,
SUMMARY_FLAG, ENABLED_FLAG
stores information about
requisition headers. You need one row for each requisition header you
create. Each row contains the requisition number, preparer,
status, and description.SEGMENT1 is the number you use to identify the
requisition in forms and reports(unique).
PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID,REQUISITION_HEADER_ID,
LINE_NUM,LINE_TYPE_ID,CATEGORY_ID,
ITEM_DESCRIPTION,UNIT_MEAS_LOOKUP_CODE
,
UNIT_PRICE, QUANTITY, DELIVER_TO_LOCATION_ID,
TO_PERSON_ID,
SOURCE_TYPE_CODE
stores
information about requisition lines.line number, item number, item category,
item description,
need–by date, deliver–to location, item quantities, units,
prices, requestor, notes, and suggested supplier information for the
requisition line.
LINE_LOCATION_ID
- purchase order shipment line on which you placed the requisition. it is null
if you
have not placed the requisition line on a purchase order.
BLANKET_PO_HEADER_ID
and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement
or catalog quotation line information for the requisition
line.
PARENT_REQ_LINE_ID
contains the REQUISITION_LINE_ID from the original requisition line if you
exploded or multisourced this requisition line.
PO_HEADERS_ALL PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE,
SEGMENT1,
SUMMARY_FLAG, ENABLED_FLAG
information
for your purchasing documents.There are six types of documents that use
PO_HEADERS_ALL
RFQs, Quotations, Standard purchase orders, Planned purchase
orders, Blanket purchase orders, Contracts
can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1
and TYPE_LOOKUP_CODE or using
PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase
orders or contract purchase orders.
if we use copy document Oracle Purchasing stores the foreign
key to your original RFQ in FROM_HEADER_ID.
PO_LINES_ALL PO_LINE_ID, PO_HEADER_ID, LINE_TYPE_ID,
LINE_NUM
stores
current information about each purchase order line. CONTRACT_NUM reference a contract
purchase order from a standard purchase order line.
PO_VENDORS VENDOR_ID, VENDOR_NAME, SEGMENT1,
SUMMARY_FLAG,
ENABLED_FLAG
information about your
suppliers.purchasing, receiving, payment, accounting, tax, classification, and
general information.
PO_VENDOR_SITES_ALL VENDOR_SITE_ID,
VENDOR_ID, VENDOR_SITE_CODE
information
about your supplier sites.a row for each supplier site you define. Each row
includes the site address, supplier reference, purchasing, payment, bank, and
general information. Oracle Purchasing uses this
information to store supplier address information.
PO_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID, PO_HEADER_ID,
PO_LINE_ID,LINE_LOCATION_ID, SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,QUANTITY_ORDERED,
DISTRIBUTION_NUM
contains accounting
distribution information fora purchase order shipment line.You need one row for
each distribution line you attach to a purchase order
shipment.
There
are four types of documents using distributions in Oracle Purchasing:
Standard Purchase Orders, Planned
Purchase Orders, Planned Purchase Order Releases, Blanket Purchase Order
Releases
includes
the destination type, requestor ID, quantity ordered and deliver–to location
for the distribution.
PO_RELEASES_ALL PO_RELEASE_ID, PO_HEADER_ID, RELEASE_NUM,
AGENT_ID,
RELEASE_DATE
contains
information about blanket and planned purchase order releases. You need one row
for each release you issue
for a
blanket or planned purchase order. Each row includes the buyer, date, release
status, and release number. Each release must have at least one purchase order
shipment
PO_VENDOR_CONTACTS VENDOR_CONTACT_ID,
VENDOR_SITE_ID
stores information about contacts for a supplier site. You
need one row for each supplier contact you define.
Each row includes the contact name and site.
PO_ACTION_HISTORY OBJECT_ID, OBJECT_TYPE_CODE ,
OBJECT_SUB_TYPE_CODE, SEQUENCE_NUM
information
about the approval and control history of your purchasing documents. There is
one record in
this table for each approval or control action an employee
takes on a purchase order, purchase agreement, release, or requisition.
stores
object_id -- Document header identifier,OBJECT_TYPE_CODE --- Document type, OBJECT_SUB_TYPE_CODE --Document subtype
SEQUENCE_NUM --Sequence of the approval or control action for a document
PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID,
REQUISITION_LINE_ID, SET_OF_BOOKS_ID,
CODE_COMBINATION_ID,REQ_LINE_QUANTITY,
DISTRIBUTION_NUM
stores
information about the accounting distributions associated with each requisition
line.
PO_LINE_LOCATIONS_ALL LINE_LOCATION_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, PO_HEADER_ID,
PO_LINE_ID, SHIPMENT_TYPE
contains
information about purchase order shipment schedules and blanket agreement price
breaks. You need one row for each
schedule or price break you attach to a document line. There are seven
types of documents that use shipment schedules:
RFQs,Quotations,Standard
purchase orders,Planned purchase orders,Planned purchase order releases,Blanket
purchase orders, Blanket purchase order releases
Each
row includes the location, quantity, and dates for each shipment schedule.
Oracle Purchasing uses
this information to record delivery schedule information for
purchase orders, and price break information for
blanket purchase orders, quotations and RFQs.
PO Interfaces
PO_HEADERS_INTERFACE
is the interface table that imports header information
from e– Commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_HEADER_ID
BATCH_ID
INTERFACE_SOURCE_CODE
PO_LINES_INTERFACE
is the interface table that imports lines information
from e– commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_LINE_ID
INTERFACE_HEADER_ID
ACTION NULL Action to be completed:
New or Add
GROUP_CODE (Null) Indicates the
grouping of the requisition lines
PO_DISTRIBUTIONS_INTERFACE
is the interface table that imports distribution
information from e– Commerce Gateway for blanket purchase orders and catalog
quotations.
INTERFACE_HEADER_ID
INTERFACE_LINE_ID
INTERFACE_DISTRIBUTION_ID
ORG_ID
PO_REQUISITIONS_INTERFACE_ALL
contains requisition information from other applications.
Import feature uses this information to
create new requisition headers, lines and distributions.
TRANSACTION_ID (PK) Transaction unique identifier
PROCESS_FLAG NULL Transaction processing state
PO_REQ_DIST_INTERFACE_ALL
Is the
interface table that creates multiple distributions using Requisition Import.
ACCRUAL_ACCOUNT_ID Unique identifier
for the General Ledger accrual account
ALLOCATION_TYPE Specifies the method
of allocation across distributions. Can be PERCENT.
ALLOCATION_VALUE Allocation split value
BATCH_ID Import batch identifier
BUDGET_ACCOUNT_ID Unique identifier for the General Ledger
budget account
CHARGE_ACCOUNT_ID Unique identifier for the General Ledger charge account
RCV_HEADERS_INTERFACE
is the
interface table that stores receiving header information.
HEADER_INTERFACE_ID Interface EDI header unique identifier
GROUP_ID NULL Interface group for set processing
EDI_CONTROL_NUM EDI transaction control number if data
is sent via EDI
PROCESSING_STATUS_CODE Processing status of the interface
header row
RECEIPT_SOURCE_CODE Source type of
the shipment
ASN_TYPE NULL The document type: values are
ASN, ASBN, or RECEIVE
TRANSACTION_TYPE The
transaction purpose code: values are
NEW, REPLACE, ADD,or CANCEL
RCV_LOTS_INTERFACE
Holds temporary
lot number transaction records for a parent record in the RCV_TRANSACTIONS_INTERFACE
table.
RCV_TRANSACTIONS_INTERFACE
stores
information about receiving transactions that are waiting to be processed by
the receiving transaction processor or were rejected due to an error when the transaction
processor attempted to process the transaction.
TRANSACTION_TYPE
TRANSACTION_DATE
PROCESSING_STATUS_CODE
Interface Table
--PO_REQUISITION_INTERFACE_ALL
Program Name to Launch the PO Import program
-Requisition Import program
In the first phase, the program validates your data and
derives or defaults additional information. The program generates an error message
for every validation that fails and creates a row
PO_REQUISITIONS_INTERFACE_ALL
It
contains requisition information from other applications. Each row includes all the information
necessary to create approved or
unapproved requisitions in Oracle Purchasing.
The
Oracle Purchasing Requisition Import feature uses this information to create
new requisition headers, lines and distributions.
PO_REQUISITIONS_INTERFACE_ALL
table
are identical to the corresponding columns in the
PO_REQUISITIONS_HEADERS_ALL,
PO_REQUISITION_LINES_ALL
and PO_REQ_DISTRIBUTIONS_ALL
tables.
Po_requisition_interface_all
INTERFACE_SOURCE_CODE
--VDP
,SOURCE_TYPE_CODE --INVENTORY
,DESTINATION_TYPE_CODE --EXPENSE
,AUTHORIZATION_STATUS --Status
Always Approved we will get ..
,REQ_NUMBER_SEGMENT1 --Request id
,PREPARER_ID -- ASSOCIATE ID comes from
VDP
,LINE_TYPE_ID
,LINE_TYPE --vArchar Requisition
line type name
,QUANTITY --Quantity
,UNIT_PRICE
,CHARGE_ACCOUNT_ID -- to be hard coded based on the
line type
,DESTINATION_ORGANIZATION_ID --v-org_id
,DELIVER_TO_LOCATION_ID -- location id
,DELIVER_TO_REQUESTOR_ID -- ASSOCIATE ID Same as Prepare_id
,ITEM_ID -- item
code
,ITEM_DESCRIPTION --Based on the item code description
is taken
,CATEGORY_ID
,UOM_CODE
,CURRENCY_CODE --if
this is supplied then next two should be filled
,RATE
,RATE_DATE
,RATE_TYPE
If you use Oracle Master Scheduling/MRP or a non– Oracle
MRP system with Oracle Purchasing, you may find that you need to reschedule
requisitions as your planning requirements change.
Reschedule Interface Table Since you have already loaded your requisitions into Oracle
Purchasing, you simply need to identify for Oracle Purchasing the requisition
lines you want to reschedule. After you identify each line to reschedule, you
can update the quantity and the need– by date for the corresponding requisition
line.
PO_RESCHEDULE_INTERFACE table for each change you want to make to a
requisition. Each row includes the requisition line identifier, the new
quantity, and the new need– by date for the requisition line. You run the
Requisition Reschedule program to implement the changes. Import Requisitions
from the External System to Oracle Purchasing Write a Import Program to load the external data Dump data into the some interface table .Validate the data through some PL/SQL
(Applying Business Logic---
Run Import Program
Requisition is imported and created as many
number
of requisition as successful records .
Others -if error then run exceptional report to see the
reason of failure.
PO Reports
Open
Purchase Orders Report (by Buyer)
The Open Purchase Orders Report (by Buyer) lists all or
specific open purchase orders that relate to buyers.
Overshipments
Report
The
Overshipments Report lists purchase order receipts with a quantity received
greater than the quantity ordered.
Location
Listing
The Location Listing shows internal organizations
locations and addresses. Purchasing lets you specify which locations you want
to review: Ship To, Bill To, Office, Internal, and Receiving.
Buyer
Listing
The Buyer Listing shows the buyer name, default
purchasing category, ship– to location, and effective dates of all buyers or a
selected set of buyers.
Blanket
and Planned PO Status Report
The Blanket and Planned PO Status report can be used to
review purchase order transactions for items you buy, using blanket purchase
agreements and planned purchase orders.
Cancelled
Requisitions Report
Use the Cancelled Requisitions Report to review
information on cancelled requisitions.
Invoice
Price Variance Report
The Invoice Price Variance Report shows the variance
between the invoice price and the purchase price for all inventory and work in process
related invoice distributions. Payables records invoice price variances when
the invoices are matched, approved, and posted.
Po Conversion Package
CREATE OR REPLACE PACKAGE BODY GEPS_PURCHASE_CONV AS
--G_BATCH_ID NUMBER;
--G_POXPOPDOI_REQUEST_ID number;
g_batch_id1 number;
g_batch_id2 number;
g_batch_id3 number;
g_batch_id4 number;
FUNCTION GET_APPLICATION_ID (P_APP_SHORT_NAME IN VARCHAR2)
RETURN NUMBER
IS
V_APP_ID NUMBER;
V_ERR_TEXT VARCHAR2(240);
BEGIN
SELECT APPLICATION_ID INTO V_APP_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = P_APP_SHORT_NAME;
RETURN(V_APP_ID);
END;
------------------------------------------------------------------------------------------------------
FUNCTION GET_RESP_ID (p_app_id IN NUMBER)
RETURN NUMBER
IS
v_responsibility_id NUMBER;
BEGIN
SELECT responsibility_id INTO v_responsibility_id
FROM FND_RESPONSIBILITY_VL
WHERE responsibility_name = 'Purchasing Super User'
AND application_id=p_app_id;
RETURN(v_responsibility_id);
END;
-------------------------------------------------------------------------------------------------------
PROCEDURE GEPS_PO_PRC (errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_from_po varchar2
,p_to_po varchar2
,p_batch_id number
,p_agent_id number)
AS
TYPE rec_gpd IS RECORD
( po GEPS_PO_DETAILS.po%type
,po_line GEPS_PO_DETAILS.po_line%type
,po_shipment GEPS_PO_DETAILS.po_shipment%type
);
TYPE p_gpd_tbl_type IS TABLE OF rec_gpd INDEX BY BINARY_INTEGER;
v_gpd_array p_gpd_tbl_type;
cursor po_header_cur(p_n01_op_unit number) is
SELECT poh.segment1 po,
poh.po_header_id, poh.ship_via_lookup_code,
poh.vendor_id, poh.vendor_site_id,
pvsa.vendor_site_code,poh.agent_id
FROM PO_HEADERS_ALL POH,
PO_VENDOR_SITES_ALL pvsa
WHERE poh.segment1 in (select distinct po from GEPS_PO_DETAILS where PO_TRANSFER_FLAG IS NULL and po between p_from_po and p_to_po)--and po='424360711')
AND poh.org_id = p_n01_op_unit
And poh.type_lookup_code = 'STANDARD'
AND poh.vendor_site_id = pvsa.vendor_site_id
And poh.org_id = pvsa.org_id;
CURSOR po_det_cur (p_h03_org_id IN NUMBER,p_po_header_id number, p_po_line number, p_po_shipment varchar2,p_n01_op_unit number)
IS
SELECT pol.line_num po_line, pll.shipment_num po_shipment,
pol.po_header_id,
pol.po_line_id, pol.item_id,
pll.line_location_id
FROM
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL,
MTL_SYSTEM_ITEMS_B MSI
WHERE pol.po_header_id=p_po_header_id
AND pol.line_num = p_po_line
AND pol.po_line_id = pll.po_line_id
and pll.shipment_num = p_po_shipment
AND pol.org_id = p_n01_op_unit
AND msi.inventory_item_id = pol.item_id
AND msi.organization_id = p_h03_org_id;
/*AND msi.planner_code IN ('CAP1', 'CAP2', 'CAP3',
'HUNSA2',
'HUNSA1', 'SVCSHOP', 'HUNSA4',
'FSTM', 'FGAS', 'FGEN', 'FSAL', 'HAZ',
'MISC', 'NOR', 'GRE', 'SUPERSEDE1');
*/
v_op_unit_N01 ORG_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
v_op_unit_H03 ORG_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
v_org_id_N01 ORG_ORGANIZATION_DEFINITIONS.organization_id%TYPE;
v_org_id_H03 ORG_ORGANIZATION_DEFINITIONS.organization_id%TYPE;
v_vend_site_id_h03 po_vendor_sites_all.vendor_site_id%TYPE;
v_vend_id_h03 po_vendor_sites_all.vendor_id%TYPE;
v_vendor_contact_id po_vendor_contacts.vendor_contact_id%TYPE;
v_freight_H03 org_freight_tl.freight_code%TYPE;
v_ship_to_location_id hr_locations_all.location_id%TYPE;
v_date NUMBER ;--:= TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS');
---vars for compilation status-------
l_request_number NUMBER;
l_phase VARCHAR2(20);
l_status VARCHAR2(20);
l_dev_phase VARCHAR2(20);
l_dev_status VARCHAR2(20);
l_message VARCHAR2(300);
l_retun_flag VARCHAR2(1);
l_bool_return BOOLEAN;
v_request_id NUMBER;
v_batch number;
v_po_header_interface_id number;
v_agent_id po_agents.agent_id%type;
BEGIN
--G_BATCH_ID:=v_date;
v_date:=p_batch_id;
BEGIN
SELECT operating_unit,organization_id
INTO v_op_unit_N01,v_org_id_N01
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = 'N01';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'2'||SQLERRM );
END;
BEGIN
SELECT operating_unit,organization_id
INTO v_op_unit_H03,v_org_id_H03
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = 'H03';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'3'||SQLERRM );
END;
BEGIN
SELECT location_id
INTO v_ship_to_location_id
FROM hr_locations_all
WHERE location_code = 'GAUSH03';
EXCEPTION
WHEN NO_DATA_FOUND
THEN v_ship_to_location_id := NULL;
END;
v_batch:=0;
for po_header_rec in po_header_cur(v_op_unit_N01)
loop
begin
select PO_HEADERS_INTERFACE_S.NEXTVAL
into v_po_header_interface_id
from dual ;
BEGIN
SELECT pvsa.vendor_site_id, pvsa.vendor_id
INTO v_vend_site_id_h03, v_vend_id_h03
FROM po_vendor_sites_all pvsa
WHERE pvsa.vendor_site_code = po_header_rec.vendor_site_code
AND pvsa.vendor_id = po_header_rec.vendor_id
AND pvsa.org_id = v_op_unit_H03;
BEGIN
SELECT pvc.vendor_contact_id
INTO v_vendor_contact_id
FROM po_vendor_contacts pvc
WHERE pvc.vendor_site_id = v_vend_site_id_h03
AND ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_vendor_contact_id := NULL;
END;
BEGIN
SELECT freight_code
INTO v_freight_H03
FROM org_freight_tl
WHERE UPPER(freight_code) = UPPER(po_header_rec.ship_via_lookup_code)
AND organization_id = v_org_id_H03
AND LANGUAGE = 'US';
EXCEPTION WHEN OTHERS THEN
v_freight_H03 := NULL;
-- fnd_file.put_line(fnd_file.LOG,'9'||SQLERRM );
END;
begin
v_agent_id:=null;
SELECT pa.agent_id
into v_agent_id
FROM po_agents pa
,PER_ALL_PEOPLE_F paf
WHERE pa.agent_id=po_header_rec.agent_id
and paf.PERSON_ID=pa.agent_id
AND SYSDATE BETWEEN pa.start_date_active AND nvl(pa.end_date_active,sysdate+1)
and sysdate between paf.effective_start_date and nvl(paf.effective_end_date,sysdate+1);
if v_agent_id=16016 then
v_agent_id:=p_agent_id;
end if;
exception
when no_data_found then
v_agent_id:=p_agent_id;
end;
-- fnd_file.put_line(1,'headers int loop');
/********* Insert into Headers Interface Table ************/
INSERT INTO po_headers_interface(interface_header_id,
action,
org_id,
BATCH_ID,
document_type_code,
vendor_id,
vendor_site_code,
vendor_site_id,
vendor_contact_id,
--effective_date,
--expiration_date,
load_sourcing_rules_flag,
agent_id,
document_subtype,
currency_code,
ship_to_location,
--bill_to_location_id,
document_num,
comments,
reply_date,
approval_required_flag,
terms_id,
process_code,
vendor_doc_num,
from_rfq_num,
freight_terms,
fob,
freight_carrier,
reply_method,
note_to_vendor,
quote_warning_delay,
approval_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute15--,
-- LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN
)
SELECT v_po_header_interface_id,--PO_HEADERS_INTERFACE_S.NEXTVAL,--interface_header_id,
'ADD',--action,
v_op_unit_H03,--org_id,
v_date,--BATCH_ID,
type_lookup_code,--document_type_code,
vendor_id,
po_header_rec.vendor_site_code,--vendor_site_code,
v_vend_site_id_H03,--vendor_site_id,
v_vendor_contact_id, --vendor_contact_id,
--SYSDATE, --effective_date,
--SYSDATE+365,--expiration_date,
'N',--load_sourcing_rules_flag,
v_agent_id,--agent_id,
NULL,--document_subtype,
currency_code,
'GAUSH03',--ship_to_location,
--bill_to_location_id,
NULL,--document_num,
comments,
reply_date,
approval_required_flag,
terms_id,
'PENDING',--process_code,
quote_vendor_quote_number,--vendor_doc_num,
from_header_id, --from_rfq_num,
freight_terms_lookup_code,--freight_terms,
fob_lookup_code,--fob,
v_freight_H03,--freight_carrier,
reply_method_lookup_code,--reply_method,
note_to_vendor,
quote_warning_delay,
'INCOMPLETE',--approval_status,
v_op_unit_H03,--attribute_category,
null,--attribute1,
null,--attribute2,
null,--attribute3,
po_header_id--, --attribute15,
-- SYSDATE, --LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- SYSDATE, --CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN
FROM po_headers_all
WHERE po_header_id = po_header_rec.po_header_id;
begin
select po,po_line, po_shipment
BULK COLLECT INTO v_gpd_array
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG IS NULL
and po=po_header_rec.po;
exception
when no_data_found then
null;
end;
FOR i IN v_gpd_array.first ..v_gpd_array.last
loop
--fnd_file.put_line(1,'after array loop');
FOR po_det_rec IN po_det_cur (v_org_id_H03,po_header_rec.po_header_id,v_gpd_array(i).po_line,v_gpd_array(i).po_shipment,v_op_unit_N01)
LOOP
-- fnd_file.put_line(1,'In loop');
v_batch:=v_batch+1;
/********* Insert into Headers Interface Table ************/
/********* Insert into Lines Interface Table ************/
INSERT INTO po_lines_interface(interface_line_id,
interface_header_id,
action,
ship_to_organization_id,
ship_to_location_id,
item_id,
unit_price,
unit_of_measure,
document_num,
item_description,
line_num,
min_order_quantity,
max_order_quantity,
vendor_product_num,
note_to_vendor,
un_number_id,
quantity,
price_discount,
hazard_class_id,
item_revision,
line_type_id,
--line_attribute1,
line_attribute15,
shipment_attribute_category,
--effective_date,
-- expiration_date,
------- quantity_billed ,
shipment_num,
promised_date,
need_by_date,
note_to_receiver,
tax_name,
receive_close_tolerance,
invoice_close_tolerance,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
SHIPMENT_ATTRIBUTE15--,
-- LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN
)
SELECT po_lines_interface_s.NEXTVAL,
v_po_header_interface_id,-- PO_HEADERS_INTERFACE_S.CURRVAL, --1369029
'ADD',
v_org_id_H03,
v_ship_to_location_id,
pol.item_id,
pol.unit_price,
pol.unit_meas_lookup_code,
NULL,-----rec_quot_headers.segment1,
pol.item_description,
pol.line_num,
pol.min_order_quantity,
pol.max_order_quantity,
pol.vendor_product_num,
'Replacement of PO# '||po_header_rec.po||' Line# '||pol.line_num,----pol.note_to_vendor,
-- po_header_rec.po||'.'||pol.line_num ||' '|| 'Replaced',----pol.note_to_vendor,
pol.un_number_id,
pol.quantity,
pll.price_discount,
pol.hazard_class_id,
pol.item_revision,
pol.line_type_id,
--'GEPSQUOTPOLINECONVADD',
pol.po_line_id,
v_op_unit_H03,
-- SYSDATE,
-- SYSDATE+365,
----- pol.quantity_billed ,
pll.shipment_num,
pll.promised_date,
pll.need_by_date,
pll.note_to_receiver,
pll.tax_name,
pll.receive_close_tolerance,
pll.invoice_close_tolerance,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.enforce_ship_to_location_code,
pll.attribute1,
pll.attribute2,
pll.attribute3,
pll.attribute4,
pll.attribute5,
'MANUAL',--pll.attribute6,
pll.LINE_LOCATION_ID--,
-- SYSDATE,
-- pol.last_updated_by,
-- SYSDATE,
-- pol.created_by,
-- pol.last_update_login
FROM po_lines_all pol, po_line_locations_all pll
WHERE pol.po_line_id = pll.po_line_id
AND pll.line_location_id = po_det_rec.line_location_id;
-- fnd_file.put_line(1,'distributions int loop');
/********* Insert into Lines Interface Table ************/
/********* Insert into Distributions Interface Table ************/
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
DISTRIBUTION_NUM,
ORG_ID,
QUANTITY_ORDERED,
CHARGE_ACCOUNT_ID,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
---- DESTINATION_TYPE_CODE,
----- DESTINATION_SUBINVENTORY,
REQ_HEADER_REFERENCE_NUM,
REQ_LINE_REFERENCE_NUM,
RATE_DATE,
attribute1,
-- LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN,
attribute15)
SELECT v_po_header_interface_id,--PO_HEADERS_INTERFACE_S.CURRVAL,
po_lines_interface_s.CURRVAL,
PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
po_header_id,
po_line_id,
DISTRIBUTION_NUM,
v_op_unit_H03,
QUANTITY_ORDERED,
null,--code_combination_id,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
-------- rec_disttribution.DESTINATION_TYPE_CODE,
--------- rec_disttribution.DESTINATION_SUBINVENTORY,
REQ_HEADER_REFERENCE_NUM,
REQ_LINE_REFERENCE_NUM,
RATE_DATE,
attribute1,
-- SYSDATE,
-- last_updated_by,
-- SYSDATE,
-- created_by,
-- last_update_login,
PO_DISTRIBUTION_ID
FROM po_distributions_all
WHERE po_header_id = po_header_rec.po_header_id
AND po_line_id = po_det_rec.po_line_id;
/********* Insert into Distributions Interface Table ************/
begin
UPDATE GEPS_PO_DETAILS
SET po_transfer_flag = 'SUCCESS'
,po_header_id = po_header_rec.po_header_id
, po_line_id = po_det_rec.po_line_id
, line_location_id = po_det_rec.line_location_id
WHERE po = po_header_rec.po
AND po_line = po_det_rec.po_line
AND po_shipment = po_det_rec.po_shipment;
exception
when others then
null;
fnd_file.put_line(fnd_file.LOG, 'when others then PO#'||po_header_rec.po||' Line # '||po_det_rec.po_line||' Shipment '||po_det_rec.po_shipment);
fnd_file.put_line(fnd_file.LOG, 'PO Header ID '||po_header_rec.po_header_id||' Line ID '||po_det_rec.po_line_id||' Line Location ID '||po_det_rec.line_location_id);
end;
if v_batch=1000 then
commit;
v_batch:=0;
end if;
END LOOP;
-- fnd_file.put_line(1,'distributions int loop');
END LOOP;--END OF v_gpd_array LOOP
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.LOG, 'Vendor Site Validation Failed for New Org');
UPDATE geps_po_details
SET po_transfer_flag= 'E'
, error_msg = 'Vendor Site : '||po_header_rec.vendor_site_code||' not found'
WHERE po = po_header_rec.po;
END;
exception
WHEN no_data_found THEN
fnd_file.put_line(fnd_file.LOG, 'Error occured for sequence PO_HEADERS_INTERFACE_S.NEXTVAL ');
UPDATE geps_po_details
SET po_transfer_flag= 'E'
, error_msg = 'Error occured for sequence PO_HEADERS_INTERFACE_S.NEXTVAL '
WHERE po = po_header_rec.po;
end;
end loop;--po_header loop
COMMIT;
v_request_id := fnd_request.submit_request( 'PO'
,'POXPOPDOI'
,''
,SYSDATE
,FALSE
,NULL
,'STANDARD'
,''
,'N'
,NULL
,'INCOMPLETE'
,NULL
,V_DATE
,v_op_unit_H03
,NULL);
COMMIT;
-- G_POXPOPDOI_REQUEST_ID:=v_request_id;
l_bool_return := FND_CONCURRENT.WAIT_FOR_REQUEST(v_request_id
,5
,10000
,l_phase
,l_status
,l_dev_phase
,l_dev_status
,l_message);
IF l_bool_return THEN
IF (l_dev_phase = 'COMPLETE') THEN
IF (l_dev_status = 'NORMAL') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Request '||l_request_number||' Completed Normal...');
l_retun_flag :='S';
ELSIF (l_dev_status = 'ERROR') THEN
Fnd_File.PUT_LINE(Fnd_File.LOG,'Request '||l_request_number||' Completed Error...');
l_retun_flag :='E';
ELSIF (l_dev_status = 'WARNING') THEN
Fnd_File.PUT_LINE(Fnd_File.LOG,'Request '||l_request_number||' Completed Warning...');
l_retun_flag :='W';
END IF;
END IF;
END IF;
GEPS_UPDATE_STAGING(p_from_po,p_to_po,v_request_id);
-- GEPS_ERROR_RPT;
exception
when others then
fnd_file.put_line(1,'not working'||sqlerrm);
END GEPS_PO_PRC;
PROCEDURE GEPS_UPDATE_STAGING(p_from_po varchar2,p_to_po varchar2,p_request_id number)
IS
--cursor to pick up rec which are validated
CURSOR cur_po_det
IS SELECT ROWID,po,po_line, po_shipment, po_header_id, po_line_id, line_location_id
FROM geps_po_details
WHERE PO_TRANSFER_FLAG='SUCCESS'
and H03_PO_HEADER_ID is null
and po between p_from_po and p_to_po;
--cursor to tke up n01 PO data
/* CURSOR cur_lines_det(c_po VARCHAR2,c_PO_LINE NUMBER,c_PO_SHIPMENT NUMBER)
IS
SELECT pll.LINE_LOCATION_ID, pll.PO_LINE_ID
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA ,
PO_LINE_LOCATIONS_ALL PLL
WHERE pha.segment1=c_po
AND pha.po_header_id=pla.po_header_id
AND pla.LINE_NUM=c_PO_LINE
AND pll.po_header_id= pha.po_header_id
AND pll.PO_LINE_ID=pla.PO_LINE_ID
AND PLL.SHIPMENT_NUM=c_PO_SHIPMENT;*/
v_sql_errm VARCHAR2(128);
v_line_count NUMBER:=0;
sucess_flag NUMBER;
v_h03_line_location_id NUMBER;
v_h03_po_line_id NUMBER;
v_h03_po_header_id NUMBER;
v_h03_po NUMBER;
v_h03_line NUMBER;
V_N01_NOTE_TO_VENDOR VARCHAR2(240);
v_batch number;
v_conc_req_start_time Date;
BEGIN
v_batch:=0;
BEGIN
select ACTUAL_START_DATE
INTO v_conc_req_start_time
from fnd_concurrent_requests
where request_id=p_request_id;
exception
WHEN others THEN
NULL;
END;
FOR rec_cur_po_det IN cur_po_det
LOOP
v_batch:=v_batch+1;
-- FOR rec_cur_lines_det IN cur_lines_det(rec_cur_po_det.PO,rec_cur_po_det.PO_LINE,rec_cur_po_det.PO_SHIPMENT)
-- LOOP
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO||'LINE:'||rec_cur_po_det.PO_LINE||'SHIPment:'||rec_cur_po_det.PO_SHIPMENT);
--check the POLLA table where attribute15 is the N01 PO Line Id.
-- fnd_file.put_line(1,'line location_id :' ||rec_cur_po_det.LINE_LOCATION_ID);
-- fnd_file.put_line(1,'v_conc_req_start_time :'||v_conc_req_start_time);
--fnd_file.put_line(1,'v_conc_req_start_time :'||to_char(v_conc_req_start_time,'DD-MON-YYYY HH24:MI:SS'));
BEGIN
SELECT line_location_id , po_line_id, po_header_id
INTO v_h03_line_location_id,v_h03_po_line_id,v_h03_po_header_id
FROM PO_LINE_LOCATIONS_ALL
WHERE attribute15=rec_cur_po_det.LINE_LOCATION_ID
And last_update_date>v_conc_req_start_time
AND ROWNUM=1;
sucess_flag:=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,v_h03_line_location_id||'.'||v_h03_po_line_id||'.'||v_h03_po_header_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- fnd_file.put_line(1,'no data found');
sucess_flag:=0;
v_h03_line_location_id:=NULL;
v_h03_po_line_id:=NULL;
v_h03_po_header_id:=NULL;
WHEN OTHERS THEN
-- fnd_file.put_line(1,'no others');
sucess_flag:=0;
v_h03_line_location_id:=NULL;
v_h03_po_line_id:=NULL;
v_h03_po_header_id:=NULL;
END;
-- fND_FILE.PUT_LINE(FND_FILE.LOG,'po created ');
IF sucess_flag >0 THEN
--update the N01 PO line with note to vendor as H03 PO# . Line#
-- fND_FILE.PUT_LINE(FND_FILE.LOG,'cancel the po');
BEGIN
SELECT poha.segment1,pola.line_num
INTO v_h03_po, v_h03_line
FROM po_headers_all poha, po_lines_all pola
WHERE poha.po_header_id=v_h03_po_header_id
AND pola.po_line_id=v_h03_po_line_id
AND poha.po_header_id=pola.po_header_id;
EXCEPTION
WHEN OTHERS THEN
v_h03_po:=NULL;
v_h03_line:=NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while finding v_h03_po Po:'||SQLERRM);
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,v_h03_po||'.'||v_h03_line );
v_n01_note_to_vendor:='Replaced by PO# '||v_h03_po||' Line# '||v_h03_line;
FND_FILE.PUT_LINE(FND_FILE.LOG,v_n01_note_to_vendor);
BEGIN
FOR po_rec IN(
SELECT po.ROWID, po.*
FROM PO_LINES po
WHERE po.po_line_id = rec_cur_po_det.po_line_id) LOOP
PO_LINES_PKG_SUD.UPDATE_ROW
( X_Rowid => po_rec.ROWID
, X_Po_Line_Id => po_rec.Po_Line_Id
, X_Last_Update_Date => SYSDATE
, X_Last_Updated_By => FND_GLOBAL.USER_ID
, X_Po_Header_Id => po_rec.Po_Header_Id
, X_Line_Type_Id => po_rec.Line_Type_Id
, X_Line_Num => po_rec.Line_Num
, X_Last_Update_Login => FND_GLOBAL.LOGIN_ID
, X_Item_Id => po_rec.Item_Id
, X_Item_Revision => po_rec.Item_Revision
, X_Category_Id => po_rec.Category_Id
, X_Item_Description => po_rec.Item_Description
, X_Unit_Meas_Lookup_Code => po_rec.Unit_Meas_Lookup_Code
, X_Quantity_Committed => po_rec.Quantity_Committed
, X_Committed_Amount => po_rec.Committed_Amount
, X_Allow_Price_Override_Flag => po_rec.Allow_Price_Override_Flag
, X_Not_To_Exceed_Price => po_rec.Not_To_Exceed_Price
, X_List_Price_Per_Unit => po_rec.List_Price_Per_Unit
, X_Unit_Price => po_rec.Unit_Price
, X_Quantity => po_rec.Quantity
, X_Un_Number_Id => po_rec.Un_Number_Id
, X_Hazard_Class_Id => po_rec.Hazard_Class_Id
, X_Note_To_Vendor => v_n01_note_to_vendor-----<<<------
, X_From_Header_Id => po_rec.From_Header_Id
, X_From_Line_Id => po_rec.From_Line_Id
, X_Min_Order_Quantity => po_rec.Min_Order_Quantity
, X_Max_Order_Quantity => po_rec.Max_Order_Quantity
, X_Qty_Rcv_Tolerance => po_rec.Qty_Rcv_Tolerance
, X_Over_Tolerance_Error_Flag => po_rec.Over_Tolerance_Error_Flag
, X_Market_Price => po_rec.Market_Price
, X_Unordered_Flag => po_rec.Unordered_Flag
, X_Closed_Flag => po_rec.Closed_Flag
, X_User_Hold_Flag => po_rec.User_Hold_Flag
, X_Cancel_Flag => po_rec.Cancel_Flag
, X_Cancelled_By => po_rec.Cancelled_By
, X_Cancel_Date => po_rec.Cancel_Date
, X_Cancel_Reason => po_rec.Cancel_Reason
, X_Firm_Status_Lookup_Code => po_rec.Firm_Status_Lookup_Code
, X_Firm_Date => po_rec.Firm_Date
, X_Vendor_Product_Num => po_rec.Vendor_Product_Num
, X_Contract_Num => po_rec.Contract_Num
, X_Taxable_Flag => po_rec.Taxable_Flag
, X_Tax_Code_Id => po_rec.Tax_Code_Id
, X_Type_1099 => po_rec.Type_1099
, X_Capital_Expense_Flag => po_rec.Capital_Expense_Flag
, X_Negotiated_By_Preparer_Flag => po_rec.Negotiated_By_Preparer_Flag
, X_Attribute_Category => po_rec.Attribute_Category
, X_Attribute1 => po_rec.Attribute1
, X_Attribute2 => po_rec.Attribute2
, X_Attribute3 => po_rec.Attribute3
, X_Attribute4 => po_rec.Attribute4
, X_Attribute5 => po_rec.Attribute5
, X_Attribute6 => po_rec.Attribute6
, X_Attribute7 => po_rec.Attribute7
, X_Attribute8 => po_rec.Attribute8
, X_Attribute9 => po_rec.Attribute9
, X_Attribute10 => po_rec.Attribute10
, X_Reference_Num => po_rec.Reference_Num
, X_Attribute11 => po_rec.Attribute11
, X_Attribute12 => po_rec.Attribute12
, X_Attribute13 => po_rec.Attribute13
, X_Attribute14 => po_rec.Attribute14
, X_Attribute15 => po_rec.Attribute15
, X_Min_Release_Amount => po_rec.Min_Release_Amount
, X_Price_Type_Lookup_Code => po_rec.Price_Type_Lookup_Code
, X_Closed_Code => po_rec.Closed_Code
, X_Price_Break_Lookup_Code => po_rec.Price_Break_Lookup_Code
, X_Ussgl_Transaction_Code => po_rec.Ussgl_Transaction_Code
, X_Government_Context => po_rec.Government_Context
, X_Closed_Date => po_rec.Closed_Date
, X_Closed_Reason => po_rec.Closed_Reason
, X_Closed_By => po_rec.Closed_By
, X_Transaction_Reason_Code => po_rec.Transaction_Reason_Code
, X_Global_Attribute_Category => po_rec.Global_Attribute_Category
, X_Global_Attribute1 => po_rec.Global_Attribute1
, X_Global_Attribute2 => po_rec.Global_Attribute2
, X_Global_Attribute3 => po_rec.Global_Attribute3
, X_Global_Attribute4 => po_rec.Global_Attribute4
, X_Global_Attribute5 => po_rec.Global_Attribute5
, X_Global_Attribute6 => po_rec.Global_Attribute6
, X_Global_Attribute7 => po_rec.Global_Attribute7
, X_Global_Attribute8 => po_rec.Global_Attribute8
, X_Global_Attribute9 => po_rec.Global_Attribute9
, X_Global_Attribute10 => po_rec.Global_Attribute10
, X_Global_Attribute11 => po_rec.Global_Attribute11
, X_Global_Attribute12 => po_rec.Global_Attribute12
, X_Global_Attribute13 => po_rec.Global_Attribute13
, X_Global_Attribute14 => po_rec.Global_Attribute14
, X_Global_Attribute15 => po_rec.Global_Attribute15
, X_Global_Attribute16 => po_rec.Global_Attribute16
, X_Global_Attribute17 => po_rec.Global_Attribute17
, X_Global_Attribute18 => po_rec.Global_Attribute18
, X_Global_Attribute19 => po_rec.Global_Attribute19
, X_Global_Attribute20 => po_rec.Global_Attribute20
, X_Expiration_Date => po_rec.Expiration_Date
, X_Base_Uom => po_rec.Base_Uom
, X_Base_Qty => po_rec.Base_Qty
, X_Secondary_Uom => po_rec.Secondary_Uom
, X_Secondary_Qty => po_rec.Secondary_Qty
, X_Qc_Grade => po_rec.Qc_Grade
, P_MANUAL_PRICE_CHANGE_FLAG => po_rec.MANUAL_PRICE_CHANGE_FLAG
);
END LOOP;
-- COMMIT;
--Geps_Po_Approval_Pkg.GEPS_PO_UPDATE_LINE(rec_cur_lines_det.po_line_id, v_n01_note_to_vendor);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating the n01 po lines:'||SQLERRM);
END;
BEGIN
UPDATE geps_po_details
SET PO_TRANSFER_FLAG='SUCCESS', comments=v_n01_note_to_vendor
,H03_PO_HEADER_ID=v_h03_po_header_id,H03_PO_LINE_ID=v_h03_po_line_id
WHERE ROWID=rec_cur_po_det.ROWID;
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_sql_errm := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO);
END;
ELSE
BEGIN
UPDATE geps_po_details
SET PO_TRANSFER_FLAG='FAILED'
WHERE ROWID=rec_cur_po_det.ROWID;
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_sql_errm := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO||'LINE:'||rec_cur_po_det.PO_LINE||'SHIPment:'||rec_cur_po_det.PO_SHIPMENT);
END;
END IF;
-- END LOOP;
if v_batch=1000 then
commit;
v_batch:=0;
end if;
END LOOP;
commit;
END GEPS_UPDATE_STAGING;
--+--------------------------------------------------------------------------------------------------------------------------+--
PROCEDURE GEPS_ERROR_RPT IS
CURSOR cur_header is
select distinct INTERFACE_HEADER_ID
from po_headers_interface
where batch_id in (g_batch_id1,g_batch_id2,g_batch_id3,g_batch_id4)
and process_code<>'ACCEPTED';
CURSOR cur_quot_err(p_interface_header_id number) IS
SELECT segment1
FROM po_headers_all
WHERE po_header_id in
(select po_header_id from po_lines_all where po_line_id in
(select Line_attribute15 from po_lines_interface where INTERFACE_HEADER_ID = p_interface_header_id));
CURSOR cur_header_err(p_interface_header_id number)
IS
SELECT distinct COLUMN_NAME,ERROR_MESSAGE
FROM po_interface_errors
WHERE INTERFACE_HEADER_ID =p_interface_header_id;
cursor cur_custom_error is
select po,error_msg
from geps_po_details
where po_transfer_flag= 'E';
cursor cur_success_po is
select distinct po,po_line,comments
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='SUCCESS';
v_total number;
v_success_total number;
v_custom_count number;
v_interface_count number;
BEGIN
select count(1)
into v_total
from GEPS_PO_DETAILS;
fnd_file.put_line(2,'Total Number of records in staging table '||v_total);
fnd_file.put_line(2,' ');
select count(1)
into v_success_total
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='SUCCESS';
fnd_file.put_line(2,'Total Number of records successfully processed '||v_success_total);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records successfully processed are'||v_success_total);
for rec_success_po in cur_success_po
loop
fnd_file.put_line(2,'N02 po# ' ||rec_success_po.po||' N02 Line num '||rec_success_po.po_line ||' '||rec_success_po.comments);
end loop;-- end of rec_success_po
select count(1)
into v_custom_count
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='E';
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Number of records failed due to custom validation '||v_custom_count);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records failed due to custom validation are');
for rec_custom_error in cur_custom_error
loop
fnd_file.put_line(2,'PO# '||rec_custom_error.po||'Error '||rec_custom_error.error_msg);
end loop; --end of rec_custom_error
select count(1)
into v_interface_count
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='FAILED';
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Number of records failed in interface table '||v_interface_count);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records failed in interface table are ');
for rec_header in cur_header
loop
for rec_quot_err in cur_quot_err(rec_header.interface_header_id)
loop
for rec_header_err in cur_header_err(rec_header.interface_header_id)
loop
fnd_file.put_line(2,'PO# '||rec_quot_err.segment1||' COLUMN '||rec_header_err.COLUMN_NAME||'Error '||rec_header_err.ERROR_MESSAGE);
end loop;-- end of rec_header_err
end loop;-- end of rec_quot_err
end loop; ---end of rec_header
END GEPS_ERROR_RPT;
procedure geps_process_po(ERRBUF OUT VARCHAR,RETCODE OUT NUMBER,p_agent_id number)
IS
TYPE rec_po IS RECORD
( po GEPS_PO_DETAILS.po%type
);
TYPE p_po_tbl_type IS TABLE OF rec_po INDEX BY BINARY_INTEGER;
v_po_array p_po_tbl_type;
-- v_po_array p_inventory_item_tbl_type;
-- v_gim_id mtl_parameters.organization_id%TYPE;
v_break number;
v_break_first number;
v_break_last number:=0;
from_po GEPS_PO_DETAILS.po%type;
to_po GEPS_PO_DETAILS.po%type;
v_resp_appl_id NUMBER := FND_PROFILE.VALUE('RESP_APPL_ID');
v_resp_id NUMBER := FND_PROFILE.VALUE('RESP_ID');
v_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
v_request1 number;
v_request2 number;
v_request3 number;
v_request4 number;
V_INTERVAL NUMBER := 3; --seconds
V_PHASE VARCHAR2 (240);
V_STATUS VARCHAR2 (240);
V_REQUEST_PHASE VARCHAR2 (240);
V_REQUEST_STATUS VARCHAR2 (240);
V_FINISHED BOOLEAN;
V_MESSAGE VARCHAR2 (1000);
-- v_number_records number;
v_agent_id number;
BEGIN
v_agent_id:=p_agent_id;
FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id,v_resp_appl_id);
begin
execute immediate 'analyze table apps.geps_po_details compute statistics';
exception
when others then
null;
end;
g_batch_id1:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||1);
g_batch_id2:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||2);
g_batch_id3:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||3);
g_batch_id4:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||4);
begin
select distinct po
BULK COLLECT INTO v_po_array
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG IS NULL
order by po;
exception
when no_data_found then
null;
end;
--fnd_file.put_line(1,' No of records : '||v_inventory_item_array.last);
if (v_po_array.last)<4 br="br" then="then"> v_break_first:=v_po_array.first;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_po_array.last;
to_po:=v_po_array(v_break_last).po;
else
v_break :=floor(v_po_array.last/4);
-- fnd_file.put_line(1,' No of v_break records : '||v_break );
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_break_last+v_break;
to_po:=v_po_array(v_break_last).po;
end if;
--fnd_file.put_line(1,' from_po : '||from_po);
--fnd_file.put_line(1,' to_po : '||to_po);
--to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||1);
v_request1:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id1,v_agent_id);
commit;
--fnd_file.put_line(1,' from_po : '||from_po);
--fnd_file.put_line(1,' to_po : '||to_po);
if (v_po_array.last)>4 then
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_break_last+v_break;
to_po:=v_po_array(v_break_last).po;
v_request2:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id2,v_agent_id);
commit;
-- fnd_file.put_line(1,' request id : '||v_request2);
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_break_last+v_break;
to_po:=v_po_array(v_break_last).po;
v_request3:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id3,v_agent_id);
commit;
-- fnd_file.put_line(1,' request id : '||v_request3);
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_po_array.last;
to_po:=v_po_array(v_break_last).po;
v_request4:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id4,v_agent_id);
commit;
-- fnd_file.put_line(1,' request id : '||v_request4);
-- fnd_file.put_line(1,' to_po : '||to_po);
end if;
IF v_request1 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PLANNER PROCESS SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request1,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request1));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request1));
END IF;
end if;
if (v_po_array.last)>4 then
IF v_request2 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request2,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request2));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request2));
END IF;
end if;
IF v_request3 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request3,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request3));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request3));
END IF;
end if;
IF v_request4 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request4,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request4));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request4));
END IF;
end if;
END IF;
--calling or error or sucess report;
GEPS_ERROR_RPT;
end GEPS_PROCESS_PO;
END GEPS_PURCHASE_CONV;
/
--G_BATCH_ID NUMBER;
--G_POXPOPDOI_REQUEST_ID number;
g_batch_id1 number;
g_batch_id2 number;
g_batch_id3 number;
g_batch_id4 number;
FUNCTION GET_APPLICATION_ID (P_APP_SHORT_NAME IN VARCHAR2)
RETURN NUMBER
IS
V_APP_ID NUMBER;
V_ERR_TEXT VARCHAR2(240);
BEGIN
SELECT APPLICATION_ID INTO V_APP_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = P_APP_SHORT_NAME;
RETURN(V_APP_ID);
END;
------------------------------------------------------------------------------------------------------
FUNCTION GET_RESP_ID (p_app_id IN NUMBER)
RETURN NUMBER
IS
v_responsibility_id NUMBER;
BEGIN
SELECT responsibility_id INTO v_responsibility_id
FROM FND_RESPONSIBILITY_VL
WHERE responsibility_name = 'Purchasing Super User'
AND application_id=p_app_id;
RETURN(v_responsibility_id);
END;
-------------------------------------------------------------------------------------------------------
PROCEDURE GEPS_PO_PRC (errbuf OUT VARCHAR2
,retcode OUT VARCHAR2
,p_from_po varchar2
,p_to_po varchar2
,p_batch_id number
,p_agent_id number)
AS
TYPE rec_gpd IS RECORD
( po GEPS_PO_DETAILS.po%type
,po_line GEPS_PO_DETAILS.po_line%type
,po_shipment GEPS_PO_DETAILS.po_shipment%type
);
TYPE p_gpd_tbl_type IS TABLE OF rec_gpd INDEX BY BINARY_INTEGER;
v_gpd_array p_gpd_tbl_type;
cursor po_header_cur(p_n01_op_unit number) is
SELECT poh.segment1 po,
poh.po_header_id, poh.ship_via_lookup_code,
poh.vendor_id, poh.vendor_site_id,
pvsa.vendor_site_code,poh.agent_id
FROM PO_HEADERS_ALL POH,
PO_VENDOR_SITES_ALL pvsa
WHERE poh.segment1 in (select distinct po from GEPS_PO_DETAILS where PO_TRANSFER_FLAG IS NULL and po between p_from_po and p_to_po)--and po='424360711')
AND poh.org_id = p_n01_op_unit
And poh.type_lookup_code = 'STANDARD'
AND poh.vendor_site_id = pvsa.vendor_site_id
And poh.org_id = pvsa.org_id;
CURSOR po_det_cur (p_h03_org_id IN NUMBER,p_po_header_id number, p_po_line number, p_po_shipment varchar2,p_n01_op_unit number)
IS
SELECT pol.line_num po_line, pll.shipment_num po_shipment,
pol.po_header_id,
pol.po_line_id, pol.item_id,
pll.line_location_id
FROM
PO_LINES_ALL POL,
PO_LINE_LOCATIONS_ALL PLL,
MTL_SYSTEM_ITEMS_B MSI
WHERE pol.po_header_id=p_po_header_id
AND pol.line_num = p_po_line
AND pol.po_line_id = pll.po_line_id
and pll.shipment_num = p_po_shipment
AND pol.org_id = p_n01_op_unit
AND msi.inventory_item_id = pol.item_id
AND msi.organization_id = p_h03_org_id;
/*AND msi.planner_code IN ('CAP1', 'CAP2', 'CAP3',
'HUNSA2',
'HUNSA1', 'SVCSHOP', 'HUNSA4',
'FSTM', 'FGAS', 'FGEN', 'FSAL', 'HAZ',
'MISC', 'NOR', 'GRE', 'SUPERSEDE1');
*/
v_op_unit_N01 ORG_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
v_op_unit_H03 ORG_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
v_org_id_N01 ORG_ORGANIZATION_DEFINITIONS.organization_id%TYPE;
v_org_id_H03 ORG_ORGANIZATION_DEFINITIONS.organization_id%TYPE;
v_vend_site_id_h03 po_vendor_sites_all.vendor_site_id%TYPE;
v_vend_id_h03 po_vendor_sites_all.vendor_id%TYPE;
v_vendor_contact_id po_vendor_contacts.vendor_contact_id%TYPE;
v_freight_H03 org_freight_tl.freight_code%TYPE;
v_ship_to_location_id hr_locations_all.location_id%TYPE;
v_date NUMBER ;--:= TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS');
---vars for compilation status-------
l_request_number NUMBER;
l_phase VARCHAR2(20);
l_status VARCHAR2(20);
l_dev_phase VARCHAR2(20);
l_dev_status VARCHAR2(20);
l_message VARCHAR2(300);
l_retun_flag VARCHAR2(1);
l_bool_return BOOLEAN;
v_request_id NUMBER;
v_batch number;
v_po_header_interface_id number;
v_agent_id po_agents.agent_id%type;
BEGIN
--G_BATCH_ID:=v_date;
v_date:=p_batch_id;
BEGIN
SELECT operating_unit,organization_id
INTO v_op_unit_N01,v_org_id_N01
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = 'N01';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'2'||SQLERRM );
END;
BEGIN
SELECT operating_unit,organization_id
INTO v_op_unit_H03,v_org_id_H03
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = 'H03';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'3'||SQLERRM );
END;
BEGIN
SELECT location_id
INTO v_ship_to_location_id
FROM hr_locations_all
WHERE location_code = 'GAUSH03';
EXCEPTION
WHEN NO_DATA_FOUND
THEN v_ship_to_location_id := NULL;
END;
v_batch:=0;
for po_header_rec in po_header_cur(v_op_unit_N01)
loop
begin
select PO_HEADERS_INTERFACE_S.NEXTVAL
into v_po_header_interface_id
from dual ;
BEGIN
SELECT pvsa.vendor_site_id, pvsa.vendor_id
INTO v_vend_site_id_h03, v_vend_id_h03
FROM po_vendor_sites_all pvsa
WHERE pvsa.vendor_site_code = po_header_rec.vendor_site_code
AND pvsa.vendor_id = po_header_rec.vendor_id
AND pvsa.org_id = v_op_unit_H03;
BEGIN
SELECT pvc.vendor_contact_id
INTO v_vendor_contact_id
FROM po_vendor_contacts pvc
WHERE pvc.vendor_site_id = v_vend_site_id_h03
AND ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_vendor_contact_id := NULL;
END;
BEGIN
SELECT freight_code
INTO v_freight_H03
FROM org_freight_tl
WHERE UPPER(freight_code) = UPPER(po_header_rec.ship_via_lookup_code)
AND organization_id = v_org_id_H03
AND LANGUAGE = 'US';
EXCEPTION WHEN OTHERS THEN
v_freight_H03 := NULL;
-- fnd_file.put_line(fnd_file.LOG,'9'||SQLERRM );
END;
begin
v_agent_id:=null;
SELECT pa.agent_id
into v_agent_id
FROM po_agents pa
,PER_ALL_PEOPLE_F paf
WHERE pa.agent_id=po_header_rec.agent_id
and paf.PERSON_ID=pa.agent_id
AND SYSDATE BETWEEN pa.start_date_active AND nvl(pa.end_date_active,sysdate+1)
and sysdate between paf.effective_start_date and nvl(paf.effective_end_date,sysdate+1);
if v_agent_id=16016 then
v_agent_id:=p_agent_id;
end if;
exception
when no_data_found then
v_agent_id:=p_agent_id;
end;
-- fnd_file.put_line(1,'headers int loop');
/********* Insert into Headers Interface Table ************/
INSERT INTO po_headers_interface(interface_header_id,
action,
org_id,
BATCH_ID,
document_type_code,
vendor_id,
vendor_site_code,
vendor_site_id,
vendor_contact_id,
--effective_date,
--expiration_date,
load_sourcing_rules_flag,
agent_id,
document_subtype,
currency_code,
ship_to_location,
--bill_to_location_id,
document_num,
comments,
reply_date,
approval_required_flag,
terms_id,
process_code,
vendor_doc_num,
from_rfq_num,
freight_terms,
fob,
freight_carrier,
reply_method,
note_to_vendor,
quote_warning_delay,
approval_status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute15--,
-- LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN
)
SELECT v_po_header_interface_id,--PO_HEADERS_INTERFACE_S.NEXTVAL,--interface_header_id,
'ADD',--action,
v_op_unit_H03,--org_id,
v_date,--BATCH_ID,
type_lookup_code,--document_type_code,
vendor_id,
po_header_rec.vendor_site_code,--vendor_site_code,
v_vend_site_id_H03,--vendor_site_id,
v_vendor_contact_id, --vendor_contact_id,
--SYSDATE, --effective_date,
--SYSDATE+365,--expiration_date,
'N',--load_sourcing_rules_flag,
v_agent_id,--agent_id,
NULL,--document_subtype,
currency_code,
'GAUSH03',--ship_to_location,
--bill_to_location_id,
NULL,--document_num,
comments,
reply_date,
approval_required_flag,
terms_id,
'PENDING',--process_code,
quote_vendor_quote_number,--vendor_doc_num,
from_header_id, --from_rfq_num,
freight_terms_lookup_code,--freight_terms,
fob_lookup_code,--fob,
v_freight_H03,--freight_carrier,
reply_method_lookup_code,--reply_method,
note_to_vendor,
quote_warning_delay,
'INCOMPLETE',--approval_status,
v_op_unit_H03,--attribute_category,
null,--attribute1,
null,--attribute2,
null,--attribute3,
po_header_id--, --attribute15,
-- SYSDATE, --LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- SYSDATE, --CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN
FROM po_headers_all
WHERE po_header_id = po_header_rec.po_header_id;
begin
select po,po_line, po_shipment
BULK COLLECT INTO v_gpd_array
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG IS NULL
and po=po_header_rec.po;
exception
when no_data_found then
null;
end;
FOR i IN v_gpd_array.first ..v_gpd_array.last
loop
--fnd_file.put_line(1,'after array loop');
FOR po_det_rec IN po_det_cur (v_org_id_H03,po_header_rec.po_header_id,v_gpd_array(i).po_line,v_gpd_array(i).po_shipment,v_op_unit_N01)
LOOP
-- fnd_file.put_line(1,'In loop');
v_batch:=v_batch+1;
/********* Insert into Headers Interface Table ************/
/********* Insert into Lines Interface Table ************/
INSERT INTO po_lines_interface(interface_line_id,
interface_header_id,
action,
ship_to_organization_id,
ship_to_location_id,
item_id,
unit_price,
unit_of_measure,
document_num,
item_description,
line_num,
min_order_quantity,
max_order_quantity,
vendor_product_num,
note_to_vendor,
un_number_id,
quantity,
price_discount,
hazard_class_id,
item_revision,
line_type_id,
--line_attribute1,
line_attribute15,
shipment_attribute_category,
--effective_date,
-- expiration_date,
------- quantity_billed ,
shipment_num,
promised_date,
need_by_date,
note_to_receiver,
tax_name,
receive_close_tolerance,
invoice_close_tolerance,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
qty_rcv_tolerance,
qty_rcv_exception_code,
enforce_ship_to_location_code,
shipment_attribute1,
shipment_attribute2,
shipment_attribute3,
shipment_attribute4,
shipment_attribute5,
shipment_attribute6,
SHIPMENT_ATTRIBUTE15--,
-- LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN
)
SELECT po_lines_interface_s.NEXTVAL,
v_po_header_interface_id,-- PO_HEADERS_INTERFACE_S.CURRVAL, --1369029
'ADD',
v_org_id_H03,
v_ship_to_location_id,
pol.item_id,
pol.unit_price,
pol.unit_meas_lookup_code,
NULL,-----rec_quot_headers.segment1,
pol.item_description,
pol.line_num,
pol.min_order_quantity,
pol.max_order_quantity,
pol.vendor_product_num,
'Replacement of PO# '||po_header_rec.po||' Line# '||pol.line_num,----pol.note_to_vendor,
-- po_header_rec.po||'.'||pol.line_num ||' '|| 'Replaced',----pol.note_to_vendor,
pol.un_number_id,
pol.quantity,
pll.price_discount,
pol.hazard_class_id,
pol.item_revision,
pol.line_type_id,
--'GEPSQUOTPOLINECONVADD',
pol.po_line_id,
v_op_unit_H03,
-- SYSDATE,
-- SYSDATE+365,
----- pol.quantity_billed ,
pll.shipment_num,
pll.promised_date,
pll.need_by_date,
pll.note_to_receiver,
pll.tax_name,
pll.receive_close_tolerance,
pll.invoice_close_tolerance,
pll.days_early_receipt_allowed,
pll.days_late_receipt_allowed,
pll.receipt_days_exception_code,
pll.qty_rcv_tolerance,
pll.qty_rcv_exception_code,
pll.enforce_ship_to_location_code,
pll.attribute1,
pll.attribute2,
pll.attribute3,
pll.attribute4,
pll.attribute5,
'MANUAL',--pll.attribute6,
pll.LINE_LOCATION_ID--,
-- SYSDATE,
-- pol.last_updated_by,
-- SYSDATE,
-- pol.created_by,
-- pol.last_update_login
FROM po_lines_all pol, po_line_locations_all pll
WHERE pol.po_line_id = pll.po_line_id
AND pll.line_location_id = po_det_rec.line_location_id;
-- fnd_file.put_line(1,'distributions int loop');
/********* Insert into Lines Interface Table ************/
/********* Insert into Distributions Interface Table ************/
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
DISTRIBUTION_NUM,
ORG_ID,
QUANTITY_ORDERED,
CHARGE_ACCOUNT_ID,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
---- DESTINATION_TYPE_CODE,
----- DESTINATION_SUBINVENTORY,
REQ_HEADER_REFERENCE_NUM,
REQ_LINE_REFERENCE_NUM,
RATE_DATE,
attribute1,
-- LAST_UPDATE_DATE,
-- LAST_UPDATED_BY,
-- CREATION_DATE,
-- CREATED_BY,
-- LAST_UPDATE_LOGIN,
attribute15)
SELECT v_po_header_interface_id,--PO_HEADERS_INTERFACE_S.CURRVAL,
po_lines_interface_s.CURRVAL,
PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
po_header_id,
po_line_id,
DISTRIBUTION_NUM,
v_op_unit_H03,
QUANTITY_ORDERED,
null,--code_combination_id,
QUANTITY_DELIVERED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
-------- rec_disttribution.DESTINATION_TYPE_CODE,
--------- rec_disttribution.DESTINATION_SUBINVENTORY,
REQ_HEADER_REFERENCE_NUM,
REQ_LINE_REFERENCE_NUM,
RATE_DATE,
attribute1,
-- SYSDATE,
-- last_updated_by,
-- SYSDATE,
-- created_by,
-- last_update_login,
PO_DISTRIBUTION_ID
FROM po_distributions_all
WHERE po_header_id = po_header_rec.po_header_id
AND po_line_id = po_det_rec.po_line_id;
/********* Insert into Distributions Interface Table ************/
begin
UPDATE GEPS_PO_DETAILS
SET po_transfer_flag = 'SUCCESS'
,po_header_id = po_header_rec.po_header_id
, po_line_id = po_det_rec.po_line_id
, line_location_id = po_det_rec.line_location_id
WHERE po = po_header_rec.po
AND po_line = po_det_rec.po_line
AND po_shipment = po_det_rec.po_shipment;
exception
when others then
null;
fnd_file.put_line(fnd_file.LOG, 'when others then PO#'||po_header_rec.po||' Line # '||po_det_rec.po_line||' Shipment '||po_det_rec.po_shipment);
fnd_file.put_line(fnd_file.LOG, 'PO Header ID '||po_header_rec.po_header_id||' Line ID '||po_det_rec.po_line_id||' Line Location ID '||po_det_rec.line_location_id);
end;
if v_batch=1000 then
commit;
v_batch:=0;
end if;
END LOOP;
-- fnd_file.put_line(1,'distributions int loop');
END LOOP;--END OF v_gpd_array LOOP
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.LOG, 'Vendor Site Validation Failed for New Org');
UPDATE geps_po_details
SET po_transfer_flag= 'E'
, error_msg = 'Vendor Site : '||po_header_rec.vendor_site_code||' not found'
WHERE po = po_header_rec.po;
END;
exception
WHEN no_data_found THEN
fnd_file.put_line(fnd_file.LOG, 'Error occured for sequence PO_HEADERS_INTERFACE_S.NEXTVAL ');
UPDATE geps_po_details
SET po_transfer_flag= 'E'
, error_msg = 'Error occured for sequence PO_HEADERS_INTERFACE_S.NEXTVAL '
WHERE po = po_header_rec.po;
end;
end loop;--po_header loop
COMMIT;
v_request_id := fnd_request.submit_request( 'PO'
,'POXPOPDOI'
,''
,SYSDATE
,FALSE
,NULL
,'STANDARD'
,''
,'N'
,NULL
,'INCOMPLETE'
,NULL
,V_DATE
,v_op_unit_H03
,NULL);
COMMIT;
-- G_POXPOPDOI_REQUEST_ID:=v_request_id;
l_bool_return := FND_CONCURRENT.WAIT_FOR_REQUEST(v_request_id
,5
,10000
,l_phase
,l_status
,l_dev_phase
,l_dev_status
,l_message);
IF l_bool_return THEN
IF (l_dev_phase = 'COMPLETE') THEN
IF (l_dev_status = 'NORMAL') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Request '||l_request_number||' Completed Normal...');
l_retun_flag :='S';
ELSIF (l_dev_status = 'ERROR') THEN
Fnd_File.PUT_LINE(Fnd_File.LOG,'Request '||l_request_number||' Completed Error...');
l_retun_flag :='E';
ELSIF (l_dev_status = 'WARNING') THEN
Fnd_File.PUT_LINE(Fnd_File.LOG,'Request '||l_request_number||' Completed Warning...');
l_retun_flag :='W';
END IF;
END IF;
END IF;
GEPS_UPDATE_STAGING(p_from_po,p_to_po,v_request_id);
-- GEPS_ERROR_RPT;
exception
when others then
fnd_file.put_line(1,'not working'||sqlerrm);
END GEPS_PO_PRC;
PROCEDURE GEPS_UPDATE_STAGING(p_from_po varchar2,p_to_po varchar2,p_request_id number)
IS
--cursor to pick up rec which are validated
CURSOR cur_po_det
IS SELECT ROWID,po,po_line, po_shipment, po_header_id, po_line_id, line_location_id
FROM geps_po_details
WHERE PO_TRANSFER_FLAG='SUCCESS'
and H03_PO_HEADER_ID is null
and po between p_from_po and p_to_po;
--cursor to tke up n01 PO data
/* CURSOR cur_lines_det(c_po VARCHAR2,c_PO_LINE NUMBER,c_PO_SHIPMENT NUMBER)
IS
SELECT pll.LINE_LOCATION_ID, pll.PO_LINE_ID
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA ,
PO_LINE_LOCATIONS_ALL PLL
WHERE pha.segment1=c_po
AND pha.po_header_id=pla.po_header_id
AND pla.LINE_NUM=c_PO_LINE
AND pll.po_header_id= pha.po_header_id
AND pll.PO_LINE_ID=pla.PO_LINE_ID
AND PLL.SHIPMENT_NUM=c_PO_SHIPMENT;*/
v_sql_errm VARCHAR2(128);
v_line_count NUMBER:=0;
sucess_flag NUMBER;
v_h03_line_location_id NUMBER;
v_h03_po_line_id NUMBER;
v_h03_po_header_id NUMBER;
v_h03_po NUMBER;
v_h03_line NUMBER;
V_N01_NOTE_TO_VENDOR VARCHAR2(240);
v_batch number;
v_conc_req_start_time Date;
BEGIN
v_batch:=0;
BEGIN
select ACTUAL_START_DATE
INTO v_conc_req_start_time
from fnd_concurrent_requests
where request_id=p_request_id;
exception
WHEN others THEN
NULL;
END;
FOR rec_cur_po_det IN cur_po_det
LOOP
v_batch:=v_batch+1;
-- FOR rec_cur_lines_det IN cur_lines_det(rec_cur_po_det.PO,rec_cur_po_det.PO_LINE,rec_cur_po_det.PO_SHIPMENT)
-- LOOP
--FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO||'LINE:'||rec_cur_po_det.PO_LINE||'SHIPment:'||rec_cur_po_det.PO_SHIPMENT);
--check the POLLA table where attribute15 is the N01 PO Line Id.
-- fnd_file.put_line(1,'line location_id :' ||rec_cur_po_det.LINE_LOCATION_ID);
-- fnd_file.put_line(1,'v_conc_req_start_time :'||v_conc_req_start_time);
--fnd_file.put_line(1,'v_conc_req_start_time :'||to_char(v_conc_req_start_time,'DD-MON-YYYY HH24:MI:SS'));
BEGIN
SELECT line_location_id , po_line_id, po_header_id
INTO v_h03_line_location_id,v_h03_po_line_id,v_h03_po_header_id
FROM PO_LINE_LOCATIONS_ALL
WHERE attribute15=rec_cur_po_det.LINE_LOCATION_ID
And last_update_date>v_conc_req_start_time
AND ROWNUM=1;
sucess_flag:=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,v_h03_line_location_id||'.'||v_h03_po_line_id||'.'||v_h03_po_header_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- fnd_file.put_line(1,'no data found');
sucess_flag:=0;
v_h03_line_location_id:=NULL;
v_h03_po_line_id:=NULL;
v_h03_po_header_id:=NULL;
WHEN OTHERS THEN
-- fnd_file.put_line(1,'no others');
sucess_flag:=0;
v_h03_line_location_id:=NULL;
v_h03_po_line_id:=NULL;
v_h03_po_header_id:=NULL;
END;
-- fND_FILE.PUT_LINE(FND_FILE.LOG,'po created ');
IF sucess_flag >0 THEN
--update the N01 PO line with note to vendor as H03 PO# . Line#
-- fND_FILE.PUT_LINE(FND_FILE.LOG,'cancel the po');
BEGIN
SELECT poha.segment1,pola.line_num
INTO v_h03_po, v_h03_line
FROM po_headers_all poha, po_lines_all pola
WHERE poha.po_header_id=v_h03_po_header_id
AND pola.po_line_id=v_h03_po_line_id
AND poha.po_header_id=pola.po_header_id;
EXCEPTION
WHEN OTHERS THEN
v_h03_po:=NULL;
v_h03_line:=NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while finding v_h03_po Po:'||SQLERRM);
END;
FND_FILE.PUT_LINE(FND_FILE.LOG,v_h03_po||'.'||v_h03_line );
v_n01_note_to_vendor:='Replaced by PO# '||v_h03_po||' Line# '||v_h03_line;
FND_FILE.PUT_LINE(FND_FILE.LOG,v_n01_note_to_vendor);
BEGIN
FOR po_rec IN(
SELECT po.ROWID, po.*
FROM PO_LINES po
WHERE po.po_line_id = rec_cur_po_det.po_line_id) LOOP
PO_LINES_PKG_SUD.UPDATE_ROW
( X_Rowid => po_rec.ROWID
, X_Po_Line_Id => po_rec.Po_Line_Id
, X_Last_Update_Date => SYSDATE
, X_Last_Updated_By => FND_GLOBAL.USER_ID
, X_Po_Header_Id => po_rec.Po_Header_Id
, X_Line_Type_Id => po_rec.Line_Type_Id
, X_Line_Num => po_rec.Line_Num
, X_Last_Update_Login => FND_GLOBAL.LOGIN_ID
, X_Item_Id => po_rec.Item_Id
, X_Item_Revision => po_rec.Item_Revision
, X_Category_Id => po_rec.Category_Id
, X_Item_Description => po_rec.Item_Description
, X_Unit_Meas_Lookup_Code => po_rec.Unit_Meas_Lookup_Code
, X_Quantity_Committed => po_rec.Quantity_Committed
, X_Committed_Amount => po_rec.Committed_Amount
, X_Allow_Price_Override_Flag => po_rec.Allow_Price_Override_Flag
, X_Not_To_Exceed_Price => po_rec.Not_To_Exceed_Price
, X_List_Price_Per_Unit => po_rec.List_Price_Per_Unit
, X_Unit_Price => po_rec.Unit_Price
, X_Quantity => po_rec.Quantity
, X_Un_Number_Id => po_rec.Un_Number_Id
, X_Hazard_Class_Id => po_rec.Hazard_Class_Id
, X_Note_To_Vendor => v_n01_note_to_vendor-----<<<------
, X_From_Header_Id => po_rec.From_Header_Id
, X_From_Line_Id => po_rec.From_Line_Id
, X_Min_Order_Quantity => po_rec.Min_Order_Quantity
, X_Max_Order_Quantity => po_rec.Max_Order_Quantity
, X_Qty_Rcv_Tolerance => po_rec.Qty_Rcv_Tolerance
, X_Over_Tolerance_Error_Flag => po_rec.Over_Tolerance_Error_Flag
, X_Market_Price => po_rec.Market_Price
, X_Unordered_Flag => po_rec.Unordered_Flag
, X_Closed_Flag => po_rec.Closed_Flag
, X_User_Hold_Flag => po_rec.User_Hold_Flag
, X_Cancel_Flag => po_rec.Cancel_Flag
, X_Cancelled_By => po_rec.Cancelled_By
, X_Cancel_Date => po_rec.Cancel_Date
, X_Cancel_Reason => po_rec.Cancel_Reason
, X_Firm_Status_Lookup_Code => po_rec.Firm_Status_Lookup_Code
, X_Firm_Date => po_rec.Firm_Date
, X_Vendor_Product_Num => po_rec.Vendor_Product_Num
, X_Contract_Num => po_rec.Contract_Num
, X_Taxable_Flag => po_rec.Taxable_Flag
, X_Tax_Code_Id => po_rec.Tax_Code_Id
, X_Type_1099 => po_rec.Type_1099
, X_Capital_Expense_Flag => po_rec.Capital_Expense_Flag
, X_Negotiated_By_Preparer_Flag => po_rec.Negotiated_By_Preparer_Flag
, X_Attribute_Category => po_rec.Attribute_Category
, X_Attribute1 => po_rec.Attribute1
, X_Attribute2 => po_rec.Attribute2
, X_Attribute3 => po_rec.Attribute3
, X_Attribute4 => po_rec.Attribute4
, X_Attribute5 => po_rec.Attribute5
, X_Attribute6 => po_rec.Attribute6
, X_Attribute7 => po_rec.Attribute7
, X_Attribute8 => po_rec.Attribute8
, X_Attribute9 => po_rec.Attribute9
, X_Attribute10 => po_rec.Attribute10
, X_Reference_Num => po_rec.Reference_Num
, X_Attribute11 => po_rec.Attribute11
, X_Attribute12 => po_rec.Attribute12
, X_Attribute13 => po_rec.Attribute13
, X_Attribute14 => po_rec.Attribute14
, X_Attribute15 => po_rec.Attribute15
, X_Min_Release_Amount => po_rec.Min_Release_Amount
, X_Price_Type_Lookup_Code => po_rec.Price_Type_Lookup_Code
, X_Closed_Code => po_rec.Closed_Code
, X_Price_Break_Lookup_Code => po_rec.Price_Break_Lookup_Code
, X_Ussgl_Transaction_Code => po_rec.Ussgl_Transaction_Code
, X_Government_Context => po_rec.Government_Context
, X_Closed_Date => po_rec.Closed_Date
, X_Closed_Reason => po_rec.Closed_Reason
, X_Closed_By => po_rec.Closed_By
, X_Transaction_Reason_Code => po_rec.Transaction_Reason_Code
, X_Global_Attribute_Category => po_rec.Global_Attribute_Category
, X_Global_Attribute1 => po_rec.Global_Attribute1
, X_Global_Attribute2 => po_rec.Global_Attribute2
, X_Global_Attribute3 => po_rec.Global_Attribute3
, X_Global_Attribute4 => po_rec.Global_Attribute4
, X_Global_Attribute5 => po_rec.Global_Attribute5
, X_Global_Attribute6 => po_rec.Global_Attribute6
, X_Global_Attribute7 => po_rec.Global_Attribute7
, X_Global_Attribute8 => po_rec.Global_Attribute8
, X_Global_Attribute9 => po_rec.Global_Attribute9
, X_Global_Attribute10 => po_rec.Global_Attribute10
, X_Global_Attribute11 => po_rec.Global_Attribute11
, X_Global_Attribute12 => po_rec.Global_Attribute12
, X_Global_Attribute13 => po_rec.Global_Attribute13
, X_Global_Attribute14 => po_rec.Global_Attribute14
, X_Global_Attribute15 => po_rec.Global_Attribute15
, X_Global_Attribute16 => po_rec.Global_Attribute16
, X_Global_Attribute17 => po_rec.Global_Attribute17
, X_Global_Attribute18 => po_rec.Global_Attribute18
, X_Global_Attribute19 => po_rec.Global_Attribute19
, X_Global_Attribute20 => po_rec.Global_Attribute20
, X_Expiration_Date => po_rec.Expiration_Date
, X_Base_Uom => po_rec.Base_Uom
, X_Base_Qty => po_rec.Base_Qty
, X_Secondary_Uom => po_rec.Secondary_Uom
, X_Secondary_Qty => po_rec.Secondary_Qty
, X_Qc_Grade => po_rec.Qc_Grade
, P_MANUAL_PRICE_CHANGE_FLAG => po_rec.MANUAL_PRICE_CHANGE_FLAG
);
END LOOP;
-- COMMIT;
--Geps_Po_Approval_Pkg.GEPS_PO_UPDATE_LINE(rec_cur_lines_det.po_line_id, v_n01_note_to_vendor);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating the n01 po lines:'||SQLERRM);
END;
BEGIN
UPDATE geps_po_details
SET PO_TRANSFER_FLAG='SUCCESS', comments=v_n01_note_to_vendor
,H03_PO_HEADER_ID=v_h03_po_header_id,H03_PO_LINE_ID=v_h03_po_line_id
WHERE ROWID=rec_cur_po_det.ROWID;
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_sql_errm := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO);
END;
ELSE
BEGIN
UPDATE geps_po_details
SET PO_TRANSFER_FLAG='FAILED'
WHERE ROWID=rec_cur_po_det.ROWID;
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_sql_errm := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO||'LINE:'||rec_cur_po_det.PO_LINE||'SHIPment:'||rec_cur_po_det.PO_SHIPMENT);
END;
END IF;
-- END LOOP;
if v_batch=1000 then
commit;
v_batch:=0;
end if;
END LOOP;
commit;
END GEPS_UPDATE_STAGING;
--+--------------------------------------------------------------------------------------------------------------------------+--
PROCEDURE GEPS_ERROR_RPT IS
CURSOR cur_header is
select distinct INTERFACE_HEADER_ID
from po_headers_interface
where batch_id in (g_batch_id1,g_batch_id2,g_batch_id3,g_batch_id4)
and process_code<>'ACCEPTED';
CURSOR cur_quot_err(p_interface_header_id number) IS
SELECT segment1
FROM po_headers_all
WHERE po_header_id in
(select po_header_id from po_lines_all where po_line_id in
(select Line_attribute15 from po_lines_interface where INTERFACE_HEADER_ID = p_interface_header_id));
CURSOR cur_header_err(p_interface_header_id number)
IS
SELECT distinct COLUMN_NAME,ERROR_MESSAGE
FROM po_interface_errors
WHERE INTERFACE_HEADER_ID =p_interface_header_id;
cursor cur_custom_error is
select po,error_msg
from geps_po_details
where po_transfer_flag= 'E';
cursor cur_success_po is
select distinct po,po_line,comments
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='SUCCESS';
v_total number;
v_success_total number;
v_custom_count number;
v_interface_count number;
BEGIN
select count(1)
into v_total
from GEPS_PO_DETAILS;
fnd_file.put_line(2,'Total Number of records in staging table '||v_total);
fnd_file.put_line(2,' ');
select count(1)
into v_success_total
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='SUCCESS';
fnd_file.put_line(2,'Total Number of records successfully processed '||v_success_total);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records successfully processed are'||v_success_total);
for rec_success_po in cur_success_po
loop
fnd_file.put_line(2,'N02 po# ' ||rec_success_po.po||' N02 Line num '||rec_success_po.po_line ||' '||rec_success_po.comments);
end loop;-- end of rec_success_po
select count(1)
into v_custom_count
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='E';
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Number of records failed due to custom validation '||v_custom_count);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records failed due to custom validation are');
for rec_custom_error in cur_custom_error
loop
fnd_file.put_line(2,'PO# '||rec_custom_error.po||'Error '||rec_custom_error.error_msg);
end loop; --end of rec_custom_error
select count(1)
into v_interface_count
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='FAILED';
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Number of records failed in interface table '||v_interface_count);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records failed in interface table are ');
for rec_header in cur_header
loop
for rec_quot_err in cur_quot_err(rec_header.interface_header_id)
loop
for rec_header_err in cur_header_err(rec_header.interface_header_id)
loop
fnd_file.put_line(2,'PO# '||rec_quot_err.segment1||' COLUMN '||rec_header_err.COLUMN_NAME||'Error '||rec_header_err.ERROR_MESSAGE);
end loop;-- end of rec_header_err
end loop;-- end of rec_quot_err
end loop; ---end of rec_header
END GEPS_ERROR_RPT;
procedure geps_process_po(ERRBUF OUT VARCHAR,RETCODE OUT NUMBER,p_agent_id number)
IS
TYPE rec_po IS RECORD
( po GEPS_PO_DETAILS.po%type
);
TYPE p_po_tbl_type IS TABLE OF rec_po INDEX BY BINARY_INTEGER;
v_po_array p_po_tbl_type;
-- v_po_array p_inventory_item_tbl_type;
-- v_gim_id mtl_parameters.organization_id%TYPE;
v_break number;
v_break_first number;
v_break_last number:=0;
from_po GEPS_PO_DETAILS.po%type;
to_po GEPS_PO_DETAILS.po%type;
v_resp_appl_id NUMBER := FND_PROFILE.VALUE('RESP_APPL_ID');
v_resp_id NUMBER := FND_PROFILE.VALUE('RESP_ID');
v_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
v_request1 number;
v_request2 number;
v_request3 number;
v_request4 number;
V_INTERVAL NUMBER := 3; --seconds
V_PHASE VARCHAR2 (240);
V_STATUS VARCHAR2 (240);
V_REQUEST_PHASE VARCHAR2 (240);
V_REQUEST_STATUS VARCHAR2 (240);
V_FINISHED BOOLEAN;
V_MESSAGE VARCHAR2 (1000);
-- v_number_records number;
v_agent_id number;
BEGIN
v_agent_id:=p_agent_id;
FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id,v_resp_appl_id);
begin
execute immediate 'analyze table apps.geps_po_details compute statistics';
exception
when others then
null;
end;
g_batch_id1:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||1);
g_batch_id2:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||2);
g_batch_id3:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||3);
g_batch_id4:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||4);
begin
select distinct po
BULK COLLECT INTO v_po_array
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG IS NULL
order by po;
exception
when no_data_found then
null;
end;
--fnd_file.put_line(1,' No of records : '||v_inventory_item_array.last);
if (v_po_array.last)<4 br="br" then="then"> v_break_first:=v_po_array.first;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_po_array.last;
to_po:=v_po_array(v_break_last).po;
else
v_break :=floor(v_po_array.last/4);
-- fnd_file.put_line(1,' No of v_break records : '||v_break );
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_break_last+v_break;
to_po:=v_po_array(v_break_last).po;
end if;
--fnd_file.put_line(1,' from_po : '||from_po);
--fnd_file.put_line(1,' to_po : '||to_po);
--to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||1);
v_request1:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id1,v_agent_id);
commit;
--fnd_file.put_line(1,' from_po : '||from_po);
--fnd_file.put_line(1,' to_po : '||to_po);
if (v_po_array.last)>4 then
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_break_last+v_break;
to_po:=v_po_array(v_break_last).po;
v_request2:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id2,v_agent_id);
commit;
-- fnd_file.put_line(1,' request id : '||v_request2);
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_break_last+v_break;
to_po:=v_po_array(v_break_last).po;
v_request3:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id3,v_agent_id);
commit;
-- fnd_file.put_line(1,' request id : '||v_request3);
v_break_first:=v_break_last+1;
from_po:=v_po_array(v_break_first).po;
v_break_last:=v_po_array.last;
to_po:=v_po_array(v_break_last).po;
v_request4:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
'',
'',
FALSE,
from_po, to_po,g_batch_id4,v_agent_id);
commit;
-- fnd_file.put_line(1,' request id : '||v_request4);
-- fnd_file.put_line(1,' to_po : '||to_po);
end if;
IF v_request1 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PLANNER PROCESS SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request1,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request1));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request1));
END IF;
end if;
if (v_po_array.last)>4 then
IF v_request2 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request2,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request2));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request2));
END IF;
end if;
IF v_request3 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request3,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request3));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request3));
END IF;
end if;
IF v_request4 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
-- END IF;
ELSE
V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID => v_request4,
INTERVAL => V_INTERVAL,
MAX_WAIT => 0,
PHASE => V_PHASE,
STATUS => V_STATUS,
DEV_PHASE => V_REQUEST_PHASE,
DEV_STATUS => V_REQUEST_STATUS,
MESSAGE => V_MESSAGE);
IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....' || ' '|| TO_CHAR (v_request4));
-- END;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM COMPLETED WITH STATUS ' ||V_REQUEST_STATUS||' '|| V_MESSAGE|| ' '|| TO_CHAR (v_request4));
END IF;
end if;
END IF;
--calling or error or sucess report;
GEPS_ERROR_RPT;
end GEPS_PROCESS_PO;
END GEPS_PURCHASE_CONV;
/
No comments:
Post a Comment