[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT last_value + 1
FROM sy_surg_ctl
WHERE key_name = 'po_id'
FOR UPDATE;*/
SELECT GEM5_PO_ID_s.nextval
FROM sys.dual;
/* UPDATE sy_surg_ctl
SET last_value = new_po_id
WHERE current of npo_id_cur; */
UPDATE sy_docs_seq
SET last_assigned = last_assigned + 1
WHERE doc_type = p_doc_type
AND orgn_code = p_orgn_code;
SELECT last_value + 1
FROM sy_surg_ctl
WHERE key_name = 'line_id'
FOR UPDATE; */
SELECT GEM5_PO_LINE_ID_s.nextval
FROM sys.dual;
SELECT GEM5_BPO_LINE_ID_s.nextval
FROM sys.dual;
SELECT last_value + 1
FROM sy_surg_ctl
WHERE key_name = 'trans_id'
FOR UPDATE; */
SELECT GEM5_TRANS_ID_s.nextval
FROM sys.dual;
/* UPDATE sy_surg_ctl
SET last_value = v_new_trans_id
WHERE current of ntrans_id_cur; */
| 18-JUL-98 Liz Enstone GEMMS 5.0 Include orgn_code in select |
| |
+============================================================================*/
FUNCTION new_po_hdr
(v_po_no IN PO_ORDR_HDR.PO_NO%TYPE,
v_orgn_code IN CPG_PURCHASING_INTERFACE.ORGN_CODE%TYPE)
RETURN BOOLEAN
IS
v_row_count NUMBER :=0;
SELECT COUNT(*)
INTO v_row_count
FROM po_ordr_hdr
WHERE po_no = v_po_no
AND orgn_code = v_orgn_code;
SELECT po_id, line_id
FROM cpg_oragems_mapping
WHERE po_header_id = header
AND po_line_id = line
AND po_line_location_id = location;
SELECT shipment_num
INTO v_line_no
FROM po_line_locations_all
WHERE line_location_id = v_po_line_location_id;
SELECT count(*)
INTO v_line_count
FROM po_lines_all
WHERE po_header_id = v_po_header_id;
SELECT count(*)
INTO v_shipment_count
FROM po_line_locations_all
WHERE po_header_id = v_po_header_id;
SELECT line_num
INTO v_line_no
FROM po_lines_all
WHERE po_line_id = v_po_line_id;
SELECT NVL(MAX(line_no),0) +1
INTO v_line_no
FROM po_ordr_dtl
WHERE po_id = v_po_id;
| last_update_date for GEMMS 5.0 |
| 04/15/99 Hasan Wahdani added app_date to v_last_update_date |
+============================================================================*/
PROCEDURE errlog_header(
v_po_no IN VARCHAR2,
v_line_num IN NUMBER,
v_shipment_num IN NUMBER,
v_revision_count IN NUMBER,
v_last_update_date IN DATE)
IS
err_msg VARCHAR2(100);
/* H. Wahdani removed format from v_last_update_date and placed a call to fnd_date.date_tocharDT */
FND_FILE.NEW_LINE(FND_FILE.LOG, 2 );
'|| fnd_date.date_to_charDT(v_last_update_date));
SELECT std_factor, um_type
FROM sy_uoms_mst
WHERE um_code = v_um;
SELECT type_factor
FROM ic_item_cnv
WHERE item_id = v_item_id
AND um_type = v_um_type;
SELECT item_um
FROM ic_item_mst
WHERE item_id = v_item_id;
| last_update_datefor GEMMS 5.0 |
| |
+============================================================================*/
FUNCTION gemms_validate
( v_orgn_code IN VARCHAR2,
v_of_payvend_site_id IN NUMBER,
v_of_shipvend_site_id IN NUMBER,
v_to_whse IN VARCHAR2,
v_billing_currency IN VARCHAR2,
v_item_no IN VARCHAR2,
v_order_um1 IN VARCHAR2,
v_price_um IN VARCHAR2,
v_order_um2 IN VARCHAR2,
v_item_um IN VARCHAR2,
v_buyer_code IN VARCHAR2,
v_from_whse IN VARCHAR2,
v_shipper_code IN VARCHAR2,
v_of_frtbill_mthd IN VARCHAR2,
v_of_terms_code IN VARCHAR2,
v_qc_grade_wanted IN VARCHAR2,
v_po_no IN VARCHAR2,
v_line_id IN NUMBER,
v_line_location_id IN NUMBER,
v_revision_count IN NUMBER,
v_last_update_date IN DATE)
RETURN BOOLEAN
IS
v_result BOOLEAN := TRUE;
SELECT co_code
FROM sy_orgn_mst
WHERE orgn_code = v_orgn_code;
SELECT line_num
FROM po_lines_all
WHERE po_line_id = v_line_id;
SELECT shipment_num
FROM po_line_locations_all
WHERE line_location_id = v_line_location_id;
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
v_revision_count, v_last_update_date);
| line where the receipt is not voided or deleted and then |
| Convert the received quantity to the base uom of the item|
| MODIFICATION HISTORY |
| 07/05/2000 Preetam Bamb created |
| |
| Uday Phadtare B1845881 deduct return qty from received qty to get correct |
| total received qty |
+==============================================================================*/
FUNCTION Get_total_Received_qty(p_po_id IN NUMBER, p_line_id IN NUMBER,p_item_id IN NUMBER,item_um1 IN VARCHAR2)
RETURN NUMBER
IS
cursor total_rcvd_qty_cur IS
Select sum(recv_qty1) qty,RECV_UM1
from po_Recv_dtl d
where po_id = p_po_id
and poline_id = p_line_id
and recv_status <> -1
group by recv_um1;
Select sum(d.return_qty1) qty, d.RETURN_UM1
from po_Rtrn_hdr h,
po_Rtrn_dtl d
where h.return_id = d.return_id
and d.po_id = p_po_id
and d.poline_id = p_line_id
and h.delete_mark <> -1
group by d.return_um1;
SELECT cost_amount
FROM po_cost_dtl
WHERE doc_type = v_type
AND pos_id = v_pos_id
AND line_id = v_line_id
AND aqui_cost_id = v_cost_id
AND delete_mark = 0;
| 11/10/98 Tony Ricci set trans_cnt on insert to '0' as per |
| OPM 11.0 |
| |
+============================================================================*/
PROCEDURE cpg_aqcst_mv
( v_po_header_id IN NUMBER,
v_po_line_id IN NUMBER,
v_line_location_id IN NUMBER,
v_po_id IN NUMBER,
v_line_id IN NUMBER,
v_doc_type IN VARCHAR2,
v_aqcst_status OUT NOCOPY BOOLEAN) -- yannamal GSCC b4403407
IS
CURSOR aqcst_cur IS
SELECT aqui_cost_id, cost_amount, incl_ind,
last_update_date, created_by, creation_date, last_updated_by,
last_update_login
FROM cpg_cost_dtl
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND line_location_id = v_line_location_id;
SELECT aqui_cost_id, cost_amount
FROM po_cost_dtl a
WHERE pos_id = v_po_id
AND line_id = v_line_id
AND NOT EXISTS (SELECT 'Y'
FROM cpg_cost_dtl b
WHERE b.po_header_id = v_po_header_id
AND b.po_line_id = v_po_line_id
AND b.line_location_id = v_line_location_id
AND b.aqui_cost_id = a.aqui_cost_id
AND b.cost_amount = a.cost_amount);
INSERT INTO po_cost_dtl
(
doc_type,
pos_id,
line_id,
aqui_cost_id,
cost_amount,
incl_ind,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
text_code,
trans_cnt,
delete_mark
)
VALUES
( v_doc_type,
v_po_id,
v_line_id,
aqcst_rec.aqui_cost_id,
aqcst_rec.cost_amount,
aqcst_rec.incl_ind,
aqcst_rec.last_update_date,
aqcst_rec.last_updated_by,
aqcst_rec.last_update_login,
aqcst_rec.created_by,
aqcst_rec.creation_date,
NULL,
0, /* T. Ricci 11/10/98 set trans_cnt to '0'*/
0
);
UPDATE po_cost_dtl
SET cost_amount = aqcst_rec.cost_amount,
incl_ind = aqcst_rec.incl_ind,
last_update_date = aqcst_rec.last_update_date
WHERE doc_type = v_doc_type
AND pos_id = v_po_id
AND line_id = v_line_id
AND aqui_cost_id = aqcst_rec.aqui_cost_id;
UPDATE po_cost_dtl
SET delete_mark = 1
WHERE pos_id = v_po_id
AND line_id = v_line_id
AND aqui_cost_id = p_aqui_cost_id
AND cost_amount = p_cost_amount;
| PROCEDURE Update_header_status |
| |
| DESCRIPTION procedure to update the header status to close or calcel |
| in case where the po has |
| only one line and that line is closed or calcelled or all the |
| lines are closed or cancelled. |
| MODIFICATION HISTORY |
| 07/26/2000 Preetam Bamb created |
| |
| |
| |
| 11/15/2001 Uday Phadtare B2068007 procedure Update_header_status. Cancel the PO
| header only if all PO lines are cancelled.
+==============================================================================*/
PROCEDURE Update_header_status(p_po_id IN NUMBER,p_cancellation_code IN VARCHAR2)
IS
cursor total_lines_cur IS
Select count(*)
From cpg_oragems_mapping
Where po_id = p_po_id;
Select po_status
From po_ordr_dtl
Where po_id = p_po_id
and po_status = 0;
Select count(*)
From po_ordr_dtl
Where po_id = p_po_id
and cancellation_code IS NULL;
update po_ordr_hdr
set po_status = 20,
cancellation_code = p_cancellation_code
where po_id = p_po_id;
update po_ordr_hdr
set po_status = 20,
cancellation_code = v_cancellation_code
where po_id = p_po_id;
| insert one. |
| 17/AUG/98 KYH Replace hard coded values with |
| appropriate system constants |
| 03/NOV/98 Tony Ricci added calls to |
| GML_PO_GLDIST.receive_data to |
| perform GL mapping |
| 06/NOV/98 Tony Ricci removed call to GML_PO_SYNCH. |
| cpg_conv_duom and replaced with |
| GMICUOM.icuomcv which is the OPM |
| standard uom conversion |
| 11/10/98 Tony Ricci set trans_cnt on insert to '0' |
| as per OPM 11.0 |
| 11/10/98 Tony Ricci added frtbill_mthd_cur so OPM |
| value will be used |
| 11/11/98 Tony Ricci removed fob_code_cur so OPM |
| value will be used (already in |
| int_rec.fob_code |
| 11/24/98 Tony Ricci added call to GMICCAL.trans_date |
| _validate to check for a valid |
| Inventory calendar |
| 02/04/99 Tony Ricci use correct variable for um2 |
| when inserting/updating |
| ic_tran_pnd BUG#814841 |
| 04/19/00 N Chekuri Added appropriate error messages |
| for Inventory calendar validation|
| routine GMICAL.trans_date_validate
| ().Bug#1274130. |
| 14/18/2001 Pushkar Upakare In ic_tran_pnd(TRANS_UM) |
| inserted primary UOM instead of |
| order UOM. |
| 05/21/2001 Uday Phadtare B1795095 In ic_tran_pnd set delete_mark|
| to 1 if po is closed and set to |
| if PO is opened. |
| 05/22/2001 P. Arvind Dath Added code to synch Attributes to|
| OPM |
| 06/28/2001 Pushkar Upakare Bug 1854280 |
| BPO table updates are corrected. |
| 10/12/01 V. Ajay Kumar BUG#2041468 Modified the update |
| statement to get the PO |
| numbers in the LOV for PO |
| NUMBER field in Receipt |
| Selection Screen. |
| 11/13/2001 Pushkar Upakare Bug 2031029 |
| Exclude PPO template record from |
| being picked up in int_cur cursor|
| 06/27/2003 Mohit Kapoor Bug 3019986 |
| Made the insertion/updation of |
| ic_summ_inv conditional, only if |
| the table exists |
+============================================================================*/
PROCEDURE cpg_int2gms( retcode out Nocopy number) -- yannamal GSCC b4403407
IS
/* T. Ricci 5/12/98 removed user_class and added new who columns*/
CURSOR int_cur IS
SELECT rowid,
item_um,
transaction_id,
transaction_type,
orgn_code,
po_no,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_status,
buyer_code,
po_id,
bpo_id,
bpo_release_number,
of_payvend_site_id,
of_shipvend_site_id,
po_date,
po_type,
from_whse,
to_whse,
recv_desc,
recv_loct,
recvaddr_id,
ship_mthd,
shipper_code,
of_frtbill_mthd,
of_terms_code,
billing_currency,
purchase_exchange_rate,
mul_div_sign,
currency_bght_fwd,
pohold_code,
cancellation_code,
fob_code,
icpurch_class,
vendso_no,
project_no,
requested_dlvdate,
sched_shipdate,
required_dlvdate,
agreed_dlvdate,
date_printed,
expedite_date,
revision_count,
in_use,
print_count,
line_id,
bpo_line_id,
apinv_line_id,
item_no,
generic_id,
item_desc,
order_qty1,
order_qty2,
order_um1,
order_um2,
received_qty1,
received_qty2,
net_price,
extended_price,
price_um,
qc_grade_wanted,
match_type,
text_code,
trans_cnt,
exported_date,
last_update_date,
created_by,
creation_date,
last_updated_by,
last_update_login,
delete_mark,
contract_value,
contract_start_date,
contract_end_date,
std_qty,
max_rels_qty,
invalid_ind,
po_release_id,
release_num,
source_shipment_id,
line_no
FROM cpg_purchasing_interface
WHERE invalid_ind ='N'
AND (transaction_type IN ('STANDARD','BLANKET') /* Bug 2031029 - Do not select TEMPLATE PPOs */
OR
(transaction_type = 'PLANNED' AND nvl(po_release_id,0) <> 0 AND nvl(release_num,0) <> 0)
)
ORDER BY transaction_id;
SELECT attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = v_po_ordr_header_id;
SELECT attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM PO_LINE_LOCATIONS_ALL
WHERE LINE_LOCATION_ID = v_line_locations_id
AND PO_HEADER_ID = v_ordr_header_id
AND PO_LINE_ID = v_po_line_id;
SELECT substrb(item_description,1,70)
FROM po_lines_all
WHERE po_header_id = v_ordr_header_id
AND po_line_id = v_line_id;
v_last_updated_by PO_RELEASES_ALL.last_updated_by%TYPE;
v_last_update_date DATE;
SELECT release_date,created_by, last_updated_by, last_update_date,agent_id
FROM po_releases_all
WHERE po_header_id = v_po_header_id
AND release_num = v_release_num;
SELECT upper(substrb(last_name ,1,35))
FROM per_people_f
WHERE person_id=v_agent_id;
SELECT bpo_id, bpo_line_id
FROM cpg_oragems_mapping
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND po_line_location_id = v_source_shipment_id;
SELECT vendor_id
FROM po_vend_mst
WHERE of_vendor_site_id = v_of_shipvend_site_id
AND co_code = v_co_code;
SELECT terms_code
FROM op_term_mst
WHERE of_terms_code = v_of_terms_code;
SELECT po_id
FROM po_ordr_hdr
WHERE po_no = v_po_no
AND orgn_code = v_orgn_code;
SELECT line_id
FROM cpg_oragems_mapping
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND po_line_location_id = v_po_line_location_id;
SELECT item_id, nvl(item_desc1,' '), item_um, item_um2, dualum_ind, noninv_ind
FROM ic_item_mst
WHERE item_no = v_item_no;
SELECT co_code
FROM sy_orgn_mst
WHERE orgn_code = v_orgn_code;
SELECT order_qty1, order_qty2,order_um1,order_um2, extended_price, line_no
FROM po_ordr_dtl
WHERE po_id = v_po_id
AND line_id = v_line_id;
SELECT po_status,cancellation_code
FROM po_ordr_dtl
WHERE po_id = v_po_id
AND line_id = v_line_id;
SELECT frtbill_mthd
FROM op_frgt_mth
WHERE of_frtbill_mthd = c_of_frtbill_mthd;
SELECT closed_code
FROM po_line_locations_all
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND line_location_id = v_po_line_locations_id;
SELECT order_qty1,received_qty1
FROM po_ordr_dtl
WHERE po_id = v_po_id
AND line_id = v_line_id;
SELECT item_id,item_um2
FROM ic_item_mst
WHERE item_no = v_item_no;
select 1
from po_ordr_dtl
where po_id = v_po_id
and line_no = v_line_no;
select oracle_username
into p_view_owner
from fnd_oracle_userid
where read_only_flag = 'U';
SELECT COUNT(*) INTO ic_summ_inv_view_exists
FROM ALL_VIEWS
WHERE VIEW_NAME = 'IC_SUMM_INV_V' and
owner = p_view_owner ;
/* T Ricci 5/12/98 changed date_modified to last_update_date*/
IF GML_PO_SYNCH.gemms_validate(
int_rec.orgn_code,
int_rec.of_payvend_site_id,
int_rec.of_shipvend_site_id,
int_rec.to_whse,
int_rec.billing_currency,
int_rec.item_no,
int_rec.order_um1,
int_rec.price_um,
int_rec.order_um2,
int_rec.item_um,
int_rec.buyer_code,
int_rec.from_whse,
int_rec.shipper_code,
int_rec.of_frtbill_mthd,
int_rec.of_terms_code,
int_rec.qc_grade_wanted,
int_rec.po_no,
int_rec.po_line_id,
int_rec.po_line_location_id,
int_rec.revision_count,
int_rec.last_update_date) = FALSE OR
v_retval < 0 THEN
UPDATE cpg_purchasing_interface
SET invalid_ind = 'Y'
WHERE rowid = int_rec.rowid;
v_last_updated_by := nvl(int_rec.last_updated_by,0);
v_last_update_date := nvl(int_rec.last_update_date,sysdate);
FETCH release_date_cur into v_release_date,v_created_by,v_last_updated_by,v_last_update_date,v_agent_id;
FETCH release_date_cur into v_release_date ,v_created_by,v_last_updated_by,v_last_update_date, v_agent_id;
/* if the first encounter with a PO, insert the header*/
/* Liz Enstone 18/AUG/98 Include orgn code parameter*/
IF GML_PO_SYNCH.new_po_hdr(v_po_no,int_rec.orgn_code) THEN
BEGIN
GML_PO_SYNCH.next_po_id(v_new_po_id, v_doc_type, int_rec.orgn_code,
v_next_id_status);
INSERT INTO po_ordr_hdr
( po_id,
orgn_code,
po_no,
bpo_id,
bpo_release_no,
po_type,
payvend_id,
shipvend_id,
recvaddr_id,
shipper_code,
recv_desc,
from_whse,
to_whse,
recv_loct,
ship_mthd,
frtbill_mthd,
purchase_exchange_rate,
mul_div_sign,
billing_currency,
currency_bght_fwd,
terms_code,
po_status,
pohold_code,
cancellation_code,
fob_code,
buyer_code,
icpurch_class,
vendso_no,
project_no,
po_date,
requested_dlvdate,
sched_shipdate,
required_dlvdate,
agreed_dlvdate,
date_printed,
expedite_date,
revision_count,
in_use,
print_count,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
delete_mark,
text_code,
exported_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14
)
VALUES /* Insertion */
( v_new_po_id,
nvl(int_rec.orgn_code,'-1'), /* orgn_code,*/
v_po_no, /* modified int_rec.po_no to v_po_no*/
v_bpo_id, /* modified int_rec.bpo_id to v_bpo_id*/
/* T. Ricci 7/6/98 NULL OK.*/
int_rec.bpo_release_number, /* LE 25/08/98 NULL OK*/
nvl(int_rec.po_type,0),
nvl(v_pay_vendor_id,0),
nvl(v_ship_vendor_id,0),
int_rec.recvaddr_id, /* T. Ricci 7/6/98 integ constraint chg*/
/* LE 25/08/98 NULL OK*/
int_rec.shipper_code,
int_rec.recv_desc, /* LE 25/08/98 NULL OK*/
int_rec.from_whse, /* T.Ricci 7/6/98 integ constraint change*/
int_rec.to_whse, /* mandatory field, checked not null*/
int_rec.recv_loct, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.ship_mthd, /* T.Ricci 7/6/98 integ constraint change*/
v_frtbill_mthd, /* LE 25/08/98 NULL OK*/
/* nvl(v_exchange_rate, 1), BUG#:1107267. Get value from cpg_purchasing_interface */
nvl(int_rec.purchase_exchange_rate, 1),
/* nvl(v_mul_div_sign, 0), BUG#:1107267. Get value from cpg_purchasing_interface */
nvl(int_rec.mul_div_sign, 0),
int_rec.billing_currency, /*mandatory field, not null*/
nvl(int_rec.currency_bght_fwd, 0),
v_terms_code, /* KYH 28/AUG/98 nullable column */
nvl(v_po_status, 0),
int_rec.pohold_code, /* T.Ricci 7/6/98 integ constraint chg*/
int_rec.cancellation_code, /* T.Ricci 7/6/98 integ constraint chg*/
int_rec.fob_code, /* LE 25/08/98 NULL OK*/
v_buyer_code, /*LE 25/08/98 NULL OK,Lswamy - BUG 1829102*/
int_rec.icpurch_class, /* T.Ricci 7/6/98 integ constraint chg*/
int_rec.vendso_no, /* LE 25/08/98 NULL OK */
int_rec.project_no, /* T.Ricci 7/6/98 integ constraint chg*/
nvl(v_po_date, SYSDATE), /* PKU - BUG 1785704 */
nvl(int_rec.requested_dlvdate, SYSDATE),
nvl(int_rec.sched_shipdate, SYSDATE),
nvl(int_rec.required_dlvdate, SYSDATE),
nvl(int_rec.agreed_dlvdate, SYSDATE),
nvl(int_rec.date_printed, SYSDATE),
int_rec.expedite_date, /* LE 25/AUG/98 NULL OK*/
int_rec.revision_count, /* LE 25/AUG/98 NULL OK*/
nvl(int_rec.in_use, 0),
int_rec.print_count, /* LE 25/AUG/98 NULL OK*/
nvl(v_creation_date,sysdate), /* PKU - BUG 1785704 */
nvl(v_created_by,0), /* lswamy - BUG 1829102 */
nvl(v_last_update_date,sysdate), /* lswamy - BUG 1829102 */
nvl(v_last_updated_by,0), /* lswamy - BUG 1829102 */
int_rec.last_update_login, /* LE 25/08/98 NULL OK*/
nvl(int_rec.delete_mark,0),
int_rec.text_code, /* T. Ricci 7/6/98 integ constraint chg*/
int_rec.exported_date, /* LE 25/08/98 NULL OK*/
attr_hdr_rec.attribute1,
attr_hdr_rec.attribute2,
attr_hdr_rec.attribute3,
attr_hdr_rec.attribute4,
attr_hdr_rec.attribute5,
attr_hdr_rec.attribute6,
attr_hdr_rec.attribute7,
attr_hdr_rec.attribute8,
attr_hdr_rec.attribute9,
attr_hdr_rec.attribute10,
attr_hdr_rec.attribute11,
attr_hdr_rec.attribute12,
attr_hdr_rec.attribute13,
attr_hdr_rec.attribute14
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_ordr_hdr')
;
/* insert the line location information*/
GML_PO_SYNCH.next_line_id('PO', v_new_line_id, v_next_id_status);
INSERT INTO po_ordr_dtl
( po_id,
line_no,
line_id,
bpo_line_id,
apinv_line_id,
item_id,
generic_id,
item_desc,
icpurch_class,
order_qty1,
order_qty2,
order_um1,
order_um2,
received_qty1,
received_qty2,
net_price,
extended_price,
price_um,
recvaddr_id,
ship_mthd,
shipper_code,
shipvend_id,
to_whse,
from_whse,
recv_loct,
recv_desc,
qc_grade_wanted,
frtbill_mthd,
terms_code,
pohold_code,
cancellation_code,
fob_code,
vendso_no,
buyer_code,
project_no,
agreed_dlvdate,
requested_dlvdate,
required_dlvdate,
sched_shipdate,
expedite_date,
po_status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
text_code,
trans_cnt,
match_type,
exported_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
( v_po_id,
v_line_no,
v_new_line_id,
v_bpo_line_id, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.apinv_line_id, /* LE 25/AUG/98 NULL OK*/
v_item_id, /*mandatory field, already checked not null*/
int_rec.generic_id, /* T.Ricci 7/7/98 integ constraint change*/
nvl(v_item_desc, ' '),
int_rec.icpurch_class, /* T.Ricci 7/7/98 integ constraint change*/
nvl(int_rec.order_qty1,0),
v_order2, /* LE 25/AUG/98 NULL OK*/
int_rec.order_um1, /*mandatory field, not null*/
v_item_um2, /* LE 25/AUG/98 NULL OK*/
int_rec.received_qty1, /* LE 25/AUG/98 NULL OK*/
int_rec.received_qty2, /* LE 25/AUG/98 NULL OK*/
nvl(int_rec.net_price,0),
nvl(int_rec.extended_price,0),
nvl(int_rec.price_um,' '),
int_rec.recvaddr_id, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.ship_mthd, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.shipper_code, /* LE 25/AUG/98 NULL OK*/
v_ship_vendor_id, /*mandatory, checked not null*/
nvl(int_rec.to_whse, ' '),
int_rec.from_whse, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.recv_loct, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.recv_desc, /* LE 25/AUG/98 NULL OK*/
int_rec.qc_grade_wanted, /* LE 25/AUG/98 NULL OK*/
v_frtbill_mthd, /* LE 25/AUG/98 NULL OK*/
v_terms_code, /* Bug 2237409 - PKU */
int_rec.pohold_code, /* T.Ricci 7/7/98 integ constraint change*/
int_rec.cancellation_code, /* T.Ricci 7/7/98 integ constraint change */
int_rec.fob_code, /* LE 25/AUG/98 NULL OK*/
int_rec.vendso_no, /* LE 25/AUG/98 NULL OK */
v_buyer_code, /* LE 25/AUG/98 NULL OK ,Lswamy - BUG 1829102*/
int_rec.project_no, /* T.Ricci 7/7/98 integ constraint change*/
nvl(int_rec.agreed_dlvdate, sysdate),
nvl(int_rec.requested_dlvdate, sysdate),
nvl(int_rec.required_dlvdate, sysdate),
nvl(int_rec.sched_shipdate, sysdate),
int_rec.expedite_date, /* LE 25/AUG/98 NULL OK*/
nvl(v_po_status,0), /*modified int_rec.po_status to v_po_status*/
nvl(v_creation_date,sysdate), /* PKU - BUG 1785704 */
nvl(v_created_by,0), /* Lswamy - BUG 1829102 */
nvl(v_last_update_date,sysdate), /* Lswamy - BUG 1829102 */
nvl(v_last_updated_by,0), /* Lswamy - BUG 1829102 */
int_rec.last_update_login, /* LE 25/AUG/98 NULL OK*/
int_rec.text_code, /* T.Ricci 7/7/98 integ constraint change*/
0, /* T. Ricci 11/10/98 set trans_cnt to '0'*/
nvl(int_rec.match_type, 3),
int_rec.exported_date, /* LE 25/AUG/98 NULL OK*/
attr_dtl_rec.attribute1,
attr_dtl_rec.attribute2,
attr_dtl_rec.attribute3,
attr_dtl_rec.attribute4,
attr_dtl_rec.attribute5,
attr_dtl_rec.attribute6,
attr_dtl_rec.attribute7,
attr_dtl_rec.attribute8,
attr_dtl_rec.attribute9,
attr_dtl_rec.attribute10,
attr_dtl_rec.attribute11,
attr_dtl_rec.attribute12,
attr_dtl_rec.attribute13,
attr_dtl_rec.attribute14,
attr_dtl_rec.attribute15
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into po_ordr_dtl');
UPDATE po_bpos_hdr
SET amount_purchased = nvl(amount_purchased,0) + nvl(int_rec.extended_price,0),
activity_ind = 1,
rel_count = nvl(rel_count,0) + 1,
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE bpo_id = v_bpo_id;
UPDATE po_bpos_dtl
SET amount_purchased = nvl(amount_purchased,0) + nvl(int_rec.extended_price,0),
qty_purchased = nvl(qty_purchased,0) + nvl(int_rec.order_qty1,0),
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE bpo_id = v_bpo_id
AND line_id = v_bpo_line_id;
/*insert into the ic_tran_pnd table*/
OPEN co_code_cur(int_rec.orgn_code);
BEGIN /*insert into ic_tran_pnd*/
/* T. Ricci 5/12/98 changed who columns for GEMMS 5.0*/
INSERT INTO ic_tran_pnd
( item_id,
line_id,
trans_id,
co_code,
orgn_code,
whse_code,
lot_id,
location,
doc_id,
doc_type,
doc_line,
line_type,
reason_code,
trans_date,
trans_qty,
trans_qty2,
qc_grade,
lot_status,
trans_stat,
trans_um,
trans_um2,
op_code,
completed_ind,
staged_ind,
gl_posted_ind,
event_id,
delete_mark,
text_code,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
)
VALUES
( v_item_id, /*mandatory, checked not null*/
v_new_line_id,
v_new_trans_id,
nvl(v_co_code, '-1'),
nvl(int_rec.orgn_code, '-1'), /*GEMMS Organization Code*/
int_rec.to_whse, /*mandatory*/
0, /*lot_id always initialized to 0*/
fnd_profile.value('IC$DEFAULT_LOCT'), /*location KYH 17/AUG/98*/
v_po_id, /*doc_id,*/
v_doc_type, /*doc_type,*/
0, /*doc_line, not used*/
0, /*line_type,*/
NULL, /*reason_code T.Ricci added NULL,*/
nvl(int_rec.agreed_dlvdate, SYSDATE), /*trans_date T.Ricci changed to*/
/* agreed_dlvdate 7/27,*/
nvl(v_order1,0), /* PPB-UOM nvl(int_rec.order_qty1, 0), trans_qty,*/
v_order2, /*trans_qty2, LE 26/AUG/98 NULL OK*/
int_rec.qc_grade_wanted, /* LE 26/AUG/98 NULL OK */
NULL, /*lot_status T.Ricci added NULL,*/
NULL, /*trans_stat, LE 26/AUG/98 NULL OK*/
v_item_um1, /* PKU - bug 1516895, not null*/
v_item_um2, /*um2 T.Ricci integ constraint change */
/*1004, op_code T.Ricci change to number,*/
nvl(int_rec.created_by, 0), /*op_code T.Ricci change to number,*/
0, /*completed_ind,*/
0, /*staged_ind,*/
0, /*gl_posted_ind,*/
0, /*event_id,*/
0, /*delete_mark,*/
NULL, /*text_code,*/
nvl(int_rec.last_update_date,sysdate), /* last_update_date,*/
nvl(int_rec.last_updated_by,0), /* last_updated_by,*/
int_rec.last_update_login, /* last_update_login, LE 26/AUG/98 NULL OK*/
nvl(int_rec.created_by,0), /* created_by,*/
nvl(v_creation_date,sysdate) /* PKU - BUG 1785704 */
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into ic_tran_pnd');
END; /* insert into ic_tran_pnd;*/
/* update the ic_summ_inv table*/
IF ic_summ_inv_view_exists = 0 THEN /* Bug 3019986 Mohit Kapoor */
/*Bug 1365777 - If item is a non-inventory item then do not update or insert
into ic_summ_inv table - PPB */
If v_noninv_ind = 0
then
BEGIN
if int_rec.qc_grade_wanted is NULL
then
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty + nvl(v_order1,0),
onpurch_qty2 = onpurch_qty2 + nvl(v_order2,0)
WHERE item_id=v_item_id
AND whse_code=int_rec.to_whse;
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty + nvl(v_order1,0),
onpurch_qty2 = onpurch_qty2 + nvl(v_order2,0)
WHERE item_id=v_item_id
AND whse_code=int_rec.to_whse
AND qc_grade = int_rec.qc_grade_wanted;
in ic_summ_inv table then insert a row in it.*/
IF (SQL%ROWCOUNT = 0) THEN
/* Get the next sequence number */
select gem5_summ_inv_id_s.nextval into l_iret from dual;
INSERT INTO ic_summ_inv
(summ_inv_id, item_id, whse_code, qc_grade,
onhand_qty, onhand_qty2, onhand_prod_qty,
onhand_prod_qty2, onhand_order_qty, onhand_order_qty2,
onhand_ship_qty, onhand_ship_qty2, onpurch_qty,
onpurch_qty2, onprod_qty, onprod_qty2,
committedsales_qty, committedsales_qty2,
committedprod_qty,
committedprod_qty2, intransit_qty, intransit_qty2,
last_updated_by, created_by, last_update_date,
creation_date)
VALUES (l_iret, v_item_id, int_rec.to_whse,
int_rec.qc_grade_wanted,
0, 0, 0, 0, 0, 0, 0, 0,
nvl(v_order1,0), nvl(v_order2,0),
0, 0, 0, 0, 0, 0, 0, 0,
0, 0, SYSDATE, SYSDATE);
END; /* update ic_summ_inv*/
/* Modified the following update statement by adding 'cancellation_code = NULL' */
UPDATE po_ordr_hdr
SET po_status = 0, cancellation_code = NULL
WHERE po_id = v_po_id;
UPDATE cpg_oragems_mapping
SET po_id = v_po_id,
line_id = v_new_line_id
WHERE po_header_id = int_rec.po_header_id
AND po_line_id = int_rec.po_line_id
AND po_line_location_id = int_rec.po_line_location_id;
/* Call GL Mapping and insert into po_dist_dtl*/
GML_PO_GLDIST.P_row_num_upd := 0 ;
UPDATE cpg_purchasing_interface
SET invalid_ind = 'Y'
WHERE rowid = int_rec.rowid;
/* T. Ricci 11/10/98 removed trans_cnt = trans_cnt + 1 from update*/
UPDATE po_ordr_dtl
SET po_status = 20,
cancellation_code = int_rec.cancellation_code,
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE po_id = v_po_id
AND line_id = v_line_id;
Update_header_status(v_po_id,int_rec.cancellation_code);
/* Bug# 1357575 - The delete_mark is set to one only of the po is cancelled added the if condition */
/* Uday Phadtare B1795095 commented the if condition */
--IF int_rec.cancellation_code IS NOT NULL THEN
UPDATE ic_tran_pnd
SET delete_mark = 1 /* only column changed by GEMMS */
WHERE doc_type = v_doc_type
AND doc_id = v_po_id
AND line_id = v_line_id;
UPDATE po_ordr_dtl
SET order_qty1 = int_rec.order_qty1, order_qty2 = v_order2
WHERE po_id = v_po_id
AND line_id = v_line_id;
/*Bug 1365777 - If item is a non-inventory item then do not update or insert
into ic_summ_inv table - PPB */
IF v_noninv_ind = 0
THEN
/* RVK */
IF (int_rec.qc_grade_wanted is not null) THEN
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
onpurch_qty2 = onpurch_qty2- (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade = int_rec.qc_grade_wanted;
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
onpurch_qty2 = onpurch_qty2- (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade is null;
UPDATE po_bpos_hdr
SET amount_purchased = amount_purchased -
nvl(int_rec.extended_price,0),
activity_ind = 1,
rel_count = rel_count + 1,
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE bpo_id = v_bpo_id;
UPDATE po_bpos_dtl
SET amount_purchased = amount_purchased -
nvl(int_rec.extended_price,0),
qty_purchased = qty_purchased -
nvl(int_rec.order_qty1,0),
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE bpo_id = v_bpo_id
AND line_id = v_bpo_line_id; /* Bug 1854280 Pushkar Upakare */
ELSE /* updates including cancel/close */
BEGIN
/* pure updates, not cancel/close*/
OPEN line_id_cur(int_rec.po_header_id, int_rec.po_line_id,
int_rec.po_line_location_id);
UPDATE po_ordr_hdr
SET payvend_id = nvl(v_pay_vendor_id,0),
shipvend_id = nvl(v_ship_vendor_id,0),
/* HW BUG#:1095846 */
po_status = nvl(v_po_status,0),
shipper_code = int_rec.shipper_code,
to_whse = int_rec.to_whse,
/* LE 26/AUG/98 NULL OK*/
/* LE 26/AUG/98 NULL OK*/
frtbill_mthd = v_frtbill_mthd,
billing_currency = int_rec.billing_currency,
/* LE 26/AUG/98 NULL OK*/
purchase_exchange_rate =
nvl(int_rec.purchase_exchange_rate, 1), /* Bug 1427876 */
terms_code = v_terms_code,
fob_code = int_rec.fob_code,
buyer_code = v_buyer_code, /* Lswamy - BUG 1829102 */
/* Bug# 1357575 PB*/
/* po_date = nvl(int_rec.po_date, SYSDATE), */
date_printed = nvl(int_rec.date_printed, SYSDATE),
/* LE 26/AUG/98 NULL OK*/
revision_count = int_rec.revision_count,
/* LE 26/AUG/98 NULL OK*/
print_count = int_rec.print_count,
last_update_date = nvl(v_last_update_date, SYSDATE), /* Lswamy - BUG 1829102 */
last_updated_by = nvl(v_last_updated_by, 0), /* Lswamy - BUG 1829102 */
/* LE 26/AUG/98 NULL OK*/
last_update_login = int_rec.last_update_login,
attribute1 = attr_hdr_rec.attribute1,
attribute2 = attr_hdr_rec.attribute2,
attribute3 = attr_hdr_rec.attribute3,
attribute4 = attr_hdr_rec.attribute4,
attribute5 = attr_hdr_rec.attribute5,
attribute6 = attr_hdr_rec.attribute6,
attribute7 = attr_hdr_rec.attribute7,
attribute8 = attr_hdr_rec.attribute8,
attribute9 = attr_hdr_rec.attribute9,
attribute10 = attr_hdr_rec.attribute10,
attribute11 = attr_hdr_rec.attribute11,
attribute12 = attr_hdr_rec.attribute12,
attribute13 = attr_hdr_rec.attribute13,
attribute14 = attr_hdr_rec.attribute14
WHERE po_id = v_po_id;
/* T. Ricci 11/10/98 removed trans_cnt = trans_cnt + 1 from update*/
/* T. Ricci 12/01/98 removed line_no from update*/
BEGIN
UPDATE po_ordr_dtl
SET item_id = v_item_id,
item_desc = nvl(v_item_desc, ' '),
order_qty1 = nvl(int_rec.order_qty1,0),
/* LE 26/AUG/98 NULL OK*/
order_qty2 = v_order2,
order_um1 = int_rec.order_um1,
/* LE 26/AUG/98 NULL OK*/
order_um2 = v_item_um2,
net_price = nvl(int_rec.net_price,0),
extended_price = nvl(int_rec.extended_price,0),
price_um = nvl(int_rec.price_um,' '),
/* LE 26/AUG/98 NULL OK*/
shipper_code = int_rec.shipper_code,
shipvend_id = v_ship_vendor_id,
to_whse = nvl(int_rec.to_whse,' '),
/* LE 26/AUG/98 NULL OK*/
qc_grade_wanted = int_rec.qc_grade_wanted,
/* LE 26/AUG/98 NULL OK*/
frtbill_mthd = v_frtbill_mthd,
/* LE 26/AUG/98 NULL OK*/
terms_code = v_terms_code,
/* LE 26/AUG/98 NULL OK*/
fob_code = int_rec.fob_code,
/* LE 26/AUG/98 NULL OK*/
buyer_code = v_buyer_code, /* Lswamy - BUG 1829102 */
agreed_dlvdate = nvl(int_rec.agreed_dlvdate, sysdate),
requested_dlvdate = nvl(int_rec.requested_dlvdate, sysdate),
required_dlvdate = nvl(int_rec.required_dlvdate, sysdate),
sched_shipdate = nvl(int_rec.sched_shipdate, sysdate),
last_update_date = nvl(v_last_update_date, SYSDATE), /* Lswamy - BUG 1829102 */
last_updated_by = nvl(v_last_updated_by, 0), /* Lswamy - BUG 1829102 */
/* LE 26/AUG/98 NULL OK*/
last_update_login = int_rec.last_update_login,
po_status = nvl(v_po_status, 0),
/* T.Ricci 7/8/98 integrity constraint change NULL's allowed*/
cancellation_code = int_rec.cancellation_code,
attribute1 = attr_dtl_rec.attribute1,
attribute2 = attr_dtl_rec.attribute2,
attribute3 = attr_dtl_rec.attribute3,
attribute4 = attr_dtl_rec.attribute4,
attribute5 = attr_dtl_rec.attribute5,
attribute6 = attr_dtl_rec.attribute6,
attribute7 = attr_dtl_rec.attribute7,
attribute8 = attr_dtl_rec.attribute8,
attribute9 = attr_dtl_rec.attribute9,
attribute10 = attr_dtl_rec.attribute10,
attribute11 = attr_dtl_rec.attribute11,
attribute12 = attr_dtl_rec.attribute12,
attribute13 = attr_dtl_rec.attribute13,
attribute14 = attr_dtl_rec.attribute14,
attribute15 = attr_dtl_rec.attribute15
WHERE po_id = v_po_id
AND line_id = v_line_id;
/* regular updates, not cancel/close*/
IF (int_rec.po_status NOT IN ('CLOSED', 'CLOSED FOR RECEIVING',
/* 'CLOSED FOR INVOICE', B1820461 */
'FINALLY CLOSED')) THEN
BEGIN
/* Added the code below to get the total received qty since only the delta should
be added to the ic_tran_pnd table - Preetam Bamb */
v_total_received_qty := Get_total_Received_qty(v_po_id,v_line_id,v_item_id,v_item_um1);
UPDATE ic_tran_pnd
SET item_id = v_item_id,
/* co_code = nvl(v_co_code, '-1'),
orgn_code = nvl(int_rec.orgn_code, '-1'), */
whse_code = int_rec.to_whse,
/* trans_qty = nvl(v_order1,0) - nvl(v_total_received_qty,0), /* PPB nvl(int_rec.order_qty1, 0), */
/* trans_qty2 = nvl(v_order2,0) - nvl(v_total_received_qty2,0),/*PPB Substracted old received qty */
/* LE 26/AUG/98 NULL OK*/
/* B1878034 update trans_qty to zero if recv_qty is more than order_qty */
trans_qty = DECODE(SIGN(nvl(v_order1,0) - nvl(v_total_received_qty,0)),
-1,0,nvl(v_order1,0) - nvl(v_total_received_qty,0)),
trans_qty2 = DECODE(SIGN(nvl(v_order2,0) - nvl(v_total_received_qty2,0)),
-1,0,nvl(v_order2,0) - nvl(v_total_received_qty2,0)),
qc_grade = int_rec.qc_grade_wanted, /* LE 26/AUG/98 NULL OK*/
trans_um = int_rec.order_um1,
trans_um2 = v_item_um2, /* LE 26/AUG/98 NULL OK*/
/** MC BUG# 1491754 update trans_date **/
trans_date = nvl(int_rec.agreed_dlvdate,SYSDATE),
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login, /* LE 26/AUG/98 NULL OK*/
delete_mark = 0 /* Uday Phadtare B1795095 */
WHERE doc_type = v_doc_type
AND doc_id = v_po_id
AND line_id = v_line_id;
END; /*update ic_tran_pnd*/
/*Bug 1365777 - If item is a non-inventory item then do not update or insert
into ic_summ_inv table - PPB */
If v_noninv_ind = 0
then
BEGIN
IF v_old_po_status = 20
THEN
/* PPB */
IF (int_rec.qc_grade_wanted is not null) THEN
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty + (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
onpurch_qty2 = onpurch_qty2+ (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade = int_rec.qc_grade_wanted;
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty + (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
onpurch_qty2 = onpurch_qty2+ (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade is null;
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty
- nvl(v_old_order_base_qty,0) /*RVK nvl(v_old_order_qty1,0) */
+ v_order1 /* RVK int_rec.order_qty1 */,
onpurch_qty2 = onpurch_qty2
- nvl(v_old_order_sec_qty,0) /* RVK v_old_order_qty2 */
+ nvl(v_order2,0)
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade IS NULL;
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty
- nvl(v_old_order_base_qty,0) /*RVK nvl(v_old_order_qty1,0) */
+ v_order1 /* RVK int_rec.order_qty1 */,
onpurch_qty2 = onpurch_qty2
- nvl(v_old_order_sec_qty,0) /* RVK v_old_order_qty2 */
+ nvl(v_order2,0)
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade = int_rec.qc_grade_wanted;
END; /*update ic_summ_inv*/
UPDATE po_bpos_hdr
SET amount_purchased =
amount_purchased - nvl(v_old_extended_price,0) +
nvl(int_rec.extended_price,0),
activity_ind = 1,
rel_count = rel_count + 1,
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE bpo_id = v_bpo_id;
/* T. Ricci 11/10/98 removed trans_cnt = trans_cnt + 1 from update*/
BEGIN
UPDATE po_bpos_dtl
SET amount_purchased =
amount_purchased - nvl(v_old_extended_price,0) +
nvl(int_rec.extended_price,0),
qty_purchased =
qty_purchased - nvl(v_old_order_qty1,0) +
nvl(int_rec.order_qty1,0),
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE bpo_id = v_bpo_id
AND line_id = v_bpo_line_id; /* Bug 1854280 Pushkar Upakare */
UPDATE po_ordr_hdr
SET revision_count = revision_count + 1,
last_update_date = nvl(int_rec.last_update_date, SYSDATE),
last_updated_by = nvl(int_rec.last_updated_by, 0),
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE po_id = v_po_id
AND revision_count = 0;
END; /*regular update*/
UPDATE ic_tran_pnd
SET delete_mark = 1 /* only column changed by GEMMS */
WHERE doc_type = v_doc_type
AND doc_id = v_po_id
AND line_id = v_line_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error inserting into ic_tran_pnd')
;
END; /*update ic_tran_pnd*/
/*Bug 1365777 - If item is a non-inventory item then do not update or insert
into ic_summ_inv table - PPB */
If v_noninv_ind = 0
then
IF int_rec.qc_grade_wanted IS NULL
THEN
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
onpurch_qty2 = onpurch_qty2 - (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade IS NULL;
UPDATE ic_summ_inv
SET onpurch_qty = onpurch_qty - (nvl(v_order1,0) - nvl(v_total_received_qty,0)),
onpurch_qty2 = onpurch_qty2 - (nvl(v_order2,0) - nvl(v_total_received_qty2,0))
WHERE item_id = v_item_id
AND whse_code = int_rec.to_whse
AND qc_grade = int_rec.qc_grade_wanted;
END;/*update ic_summ_inv*/
END; /* end update including cancel/close*/
UPDATE po_ordr_dtl /* For standard, blanket and PPOs */
SET acct_map_ind = v_acct_map_ind,
last_update_date = nvl(v_last_update_date, SYSDATE),/* lswamy - BUG 1829102 */
last_updated_by = nvl(v_last_updated_by, 0), /* lswamy - BUG 1829102 */
last_update_login = int_rec.last_update_login /* LE 26/AUG/98 NULL OK*/
WHERE po_id = v_po_id;
UPDATE cpg_purchasing_interface
SET invalid_ind = 'P' /*row processed*/
WHERE rowid = int_rec.rowid;
UPDATE cpg_purchasing_interface
SET invalid_ind = 'E' /* This line can not be inserted in po_ordr_dtl */
WHERE rowid = int_rec.rowid;