[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PROCEDURE gml_insert_recv_interface |
| |
| DESCRIPTION The procedure inserts data into the |
| RCV_HEADERS_INTERFACE and |
| RCV_TRANSACTIONS_INTERFACE tables |
| |
| MODIFICATION HISTORY |
| 10-MAR-99 Tony Ricci Created. |
| 03-NOV-99 NC Modified the opm_vendor_cur to get of_vendor_id based|
| on shipvend_id from po_ordr_hdr instead of po_recv_hdr.
| The original code was populating the rcv_headers_interface
| table with blank vendor_id and causing the Receipt |
| Transaction Processor to fail. |
| 31-MAY-00 NC Added code to create deliveries on apps side. |
| A delivery transaction is now created automatically | | with each receipt transaction. Bug#1098066 |
| 12-OCT-00 NC Pay on receipt enhancements. Bug#1518114.Replaced the | | INSERTS into rcv_headers_interface and rcv_transactions_
| interface by calles to gml_new_rcv_trans_insert in an |
| effort to eliminated redundancy in code.
| 10-DEC-01 Uday Phadtare Bug#2007945 Added parameter p_dtl_recv_date to procedure
| gml_insert_recv_interface so that transaction_date in rcv_transactions_interface
| is populated with this date.
+========================================================================*/
PROCEDURE gml_insert_recv_interface(p_recv_id IN NUMBER, p_line_id IN NUMBER,
p_po_id IN NUMBER, p_poline_id IN NUMBER,
p_opm_item_id IN NUMBER, p_recv_qty1 IN NUMBER,
p_recv_um1 IN VARCHAR2, p_dtl_recv_date IN DATE DEFAULT SYSDATE) AS
v_po_id po_ordr_hdr.po_id%TYPE;
v_last_updated_by po_recv_hdr.last_updated_by%TYPE;
SELECT RCV_HEADERS_INTERFACE_S.nextval
FROM sys.dual;
SELECT RCV_TRANSACTIONS_INTERFACE_S.nextval
FROM sys.dual;
SELECT RCV_INTERFACE_GROUPS_S.nextval
FROM sys.dual;
SELECT po_header_id, po_line_id, po_line_location_id
FROM cpg_oragems_mapping
WHERE po_id = vc_po_id AND
line_id = vc_poline_id;
SELECT ship_to_organization_id
FROM po_line_locations_all
WHERE po_header_id = vc_apps_po_header_id AND
po_line_id = vc_apps_po_line_id AND
line_location_id = vc_apps_po_line_location_id;
SELECT group_id,rcv_receipt_num
FROM gml_recv_trans_map
WHERE recv_id = v_recv_id
AND organization_id = v_ship_to_organization_id
AND rcv_receipt_num is not null;
SELECT to_char(next_receipt_num + 1 )
FROM rcv_parameters
WHERE organization_id = v_ship_to_organization_id;
v_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
UPDATE rcv_parameters
set next_receipt_num = v_rcv_receipt_num
where organization_id = v_ship_to_organization_id ;
select header_interface_id
into x_header_interface_id
from rcv_headers_interface
where group_id = x_group_id;
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,
p_line_id,
p_po_id, p_poline_id,
p_opm_item_id, p_recv_qty1,0,
p_recv_um1,NULL,0,NULL,
x_header_interface_id,
x_group_id,
v_rcv_receipt_num,0,v_dtl_recv_date);
UPDATE rcv_parameters
set next_receipt_num = v_rcv_receipt_num
where organization_id = v_ship_to_organization_id ;
/* Header Insert */
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,
p_line_id,
p_po_id, p_poline_id,
p_opm_item_id, p_recv_qty1,0,
p_recv_um1,NULL,0,NULL,
new_header_interface_id,
new_group_id,
v_rcv_receipt_num,1);
/* Transaction Insert */
/* B2007945 v_dtl_recv_date added to call */
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,
p_line_id,
p_po_id, p_poline_id,
p_opm_item_id, p_recv_qty1,0,
p_recv_um1,NULL,0,NULL,
new_header_interface_id,
new_group_id,
v_rcv_receipt_num,0,v_dtl_recv_date);
/* IF G_rows_inserted = 1 THEN
gml_recv_trans_pkg.gml_process_adjust_errors(retcode);
END gml_insert_recv_interface;
| DESCRIPTION The procedure updates data into the |
| RCV_TRANSACTIONS, RCV_SHIPMENT_LINES and |
| RCV_SUPPLY tables |
| |
| MODIFICATION HISTORY |
| 04-MAY-99 Tony Ricci Created. |
| 23-NOV-99 NC Modified.
| 12-OCT-00 NC Pay on receipt enhancements Bug#1518144.
| Replaced direct INSERT into rcv_transactions by calls
| to gml_new_rcv_trans_insert() by passing appropriate |
| parameters for different types of adjustments.
+========================================================================*/
PROCEDURE gml_adjust_recv_trans(p_recv_id IN NUMBER, p_line_id IN NUMBER,
p_po_id IN NUMBER, p_poline_id IN NUMBER,
p_opm_item_id IN NUMBER, p_recv_qty1 IN NUMBER,
p_old_recv_qty1 IN NUMBER,
p_recv_um1 IN VARCHAR2,
p_return_ind IN NUMBER,
p_recv_status IN NUMBER,
p_net_price IN NUMBER,
p_rtrn_void_ind IN NUMBER) AS
X_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
SELECT interface_transaction_id
FROM gml_recv_trans_map
WHERE recv_id = vc_recv_id AND
line_id = vc_line_id;
SELECT transaction_id
FROM rcv_transactions
WHERE parent_transaction_id = v_transaction_id AND
transaction_type = v_transaction_type AND
interface_transaction_id = v_interface_transaction_id;
SELECT transaction_id,quantity
FROM rcv_transactions
WHERE parent_transaction_id = v_transaction_id AND
transaction_type = v_transaction_type;
SELECT transaction_id
FROM rcv_transactions
WHERE interface_transaction_id = X_interface_transaction_id AND
transaction_type = 'RECEIVE';
gml_recv_trans_pkg.gml_insert_adjust_error(p_recv_id, p_line_id,
p_recv_qty1, p_old_recv_qty1,
p_recv_um1,p_return_ind,
p_recv_status,
p_rtrn_void_ind);
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,-(v_return_quantity),0,
p_recv_um1,'CORRECT',v_void_ret_parent_id,
'RECEIVING',0,0,NULL,0);
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,v_return_quantity,0,
p_recv_um1,'DELIVER',v_transaction_id,
'INVENTORY',0,0,NULL,0);
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,v_recv_qty1,0,
p_recv_um1,'CORRECT',v_deliver_transaction_id,
'INVENTORY',0,0,NULL,0);
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,v_recv_qty1,0,
p_recv_um1,'CORRECT',v_transaction_id,
'RECEIVING',0,0,NULL,0);
/* Return is pretty straight forward. We insert one
transaction 'Return To Vendor' from Inventory. and This
creates 1) Return to Receiving from Inventory and
2) Return to vendor from Receiving in rcv_transactions.
*/
ELSIF (p_return_ind = 1) THEN
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,p_recv_qty1,0,
p_recv_um1,'RETURN TO VENDOR',v_deliver_transaction_id,
'INVENTORY',0,0,NULL,0);
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,v_recv_qty1,v_old_recv_qty1,
p_recv_um1,'CORRECT',v_trans_id1,
v_destination_type1,0,0,NULL,0);
gml_recv_trans_pkg.gml_new_rcv_trans_insert(p_recv_id,p_line_id,
p_po_id,p_poline_id,
p_opm_item_id,v_recv_qty1,v_old_recv_qty1,
p_recv_um1,'CORRECT',v_trans_id2,
v_destination_type2,0,0,NULL,0 );
| PROCEDURE gml_insert_adjust_error |
| |
| DESCRIPTION The procedure inserts a row into the |
| GML_RECV_ADJUST_ERRORS table to indicate that an |
| adjustment/return was made in OPM to a receipt that does |
| not have a corresponding transaction in Oracle Receiving |
| |
| MODIFICATION HISTORY |
| 18-MAY-99 Tony Ricci Created. |
| 12-OCT-00 NC Pay on receipt enhancements.Bug#1518114 Added new |
| parameters and columns in insert statement to reflect |
| the added columns in gml_recv_adjust_errors table. |
| 20-AUG-01 P. Arvind Dath BUG#1938430 |
| Modified code to retrieve the max sequence number for a |
| given recv_id and line_id combination, to avoid primary |
| key voilation errors on the 'gml_recv_adjust_errors' table|
+========================================================================*/
PROCEDURE gml_insert_adjust_error(p_recv_id IN NUMBER, p_line_id IN NUMBER,
p_recv_qty1 IN NUMBER,
p_old_recv_qty1 IN NUMBER,
p_recv_um1 IN VARCHAR2,
p_return_ind IN NUMBER,
p_recv_status IN NUMBER,
p_rtrn_void_ind IN NUMBER) AS
X_progress VARCHAR2(4);
v_last_updated_by gml_recv_adjust_errors.last_updated_by%TYPE;
SELECT nvl(max(seq_no),0)
FROM gml_recv_adjust_errors
WHERE recv_id = vc_recv_id AND
line_id = vc_line_id;
v_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
INSERT INTO gml_recv_adjust_errors
(recv_id, line_id, seq_no,recv_qty1, old_recv_qty1, recv_um1,
return_ind,recv_status,void_return_ind,
creation_date,created_by,last_update_date,last_updated_by,
last_update_login,processed_ind)
VALUES
(p_recv_id, p_line_id, X_seq_no, p_recv_qty1, p_old_recv_qty1, p_recv_um1,
p_return_ind,p_recv_status,p_rtrn_void_ind,
SYSDATE,v_created_by, SYSDATE,v_last_updated_by,NULL,'N');
po_message_s.sql_error('gml_insert_adjust_error', X_progress, sqlcode);
END gml_insert_adjust_error;
select recv_id,
line_id,
seq_no,
recv_qty1,
old_recv_qty1,
recv_um1,
return_ind,
recv_status,
void_return_ind
from GML_RECV_ADJUST_ERRORS
where processed_ind = 'N'
/*PB 22-JUN-2000 order by added*/
order by seq_no;
SELECT interface_transaction_id
FROM gml_recv_trans_map
WHERE recv_id = vc_recv_id AND
line_id = vc_line_id;
SELECT po_id, poline_id, item_id, recv_status, return_ind, net_price
FROM po_recv_dtl
WHERE recv_id = vc_recv_id AND
line_id = vc_line_id;
update gml_recv_adjust_errors
set processed_ind = 'Y',
last_update_date = SYSDATE
where recv_id = error_rec.recv_id
and line_id = error_rec.line_id
and seq_no = error_rec.seq_no;
gml_new_recv_trans_insert()
DESCRIPTION The procedure inserts data into the
RCV_HEADERS_INTERFACE ,
RCV_TRANSACTIONS_INTERFACE and
gml_recv_trans_map tables
PARAMETERS p_recv_id recv_id
....
p_transaction_type Transaction_type could be 'DELIVER',
'RECEIVE', 'CORRECTION','RETURN TO VENDOR' etc.
p_transaction_id This is the parent_transaction_id used
mainly for corrections, This value is 0
otherwise.
p_destination_type_code Destination type of the transaction.Also
primarily used for corrections. NULL otherwise.
It could be either 'INVENTORY' or 'RECEIVING'.
p_header_interface_id This has a valid value if the transaction is
associated with a header.
0 - For corrections,
p_group_id Same as above.
0 - for corrections.
p_rcv_receipt_num Apps receipt number.
p_header_flag 1 - If this is a header insert
0 - Otherwise.
MODIFICATION HISTORY
12-OCT-00 NC Created.
This procedure inserts records into both rcv_headers_interface and
rcv_transactions_interface for receipts and all types of corrections
to receipts (adjustment to receipts/returns, voiding of receipts/returns
etc.) depending on the parameters passed.
Bug#1518114.
26-DEC-00 NC Bug#1554124 Added v_vendor_site_id in the insert clause
for rcv_transactions_interface. Auto invoices
were failing as this column was getting
populated with NULL.
10-DEC-01 Uday Phadtare Bug#2007945 Added parameter p_dtl_recv_date to procedure
gml_new_rcv_trans_insert so that transaction_date in rcv_transactions_interface
is populated with this date.
26-JUL-02 Pushkar Upakare Bug 2458366
Added waybill_no to the rcv_header_interface from po_recv_hdr
+========================================================================*/
PROCEDURE gml_new_rcv_trans_insert(p_recv_id IN NUMBER,
p_line_id IN NUMBER,
p_po_id IN NUMBER,
p_poline_id IN NUMBER,
p_opm_item_id IN NUMBER,
p_recv_qty1 IN NUMBER,
p_old_recv_qty1 IN NUMBER,
p_recv_um1 IN VARCHAR2,
p_transaction_type IN VARCHAR2,
p_transaction_id IN NUMBER,
p_destination_type_code IN VARCHAR2,
p_header_interface_id IN NUMBER,
p_group_id IN NUMBER,
p_rcv_receipt_num IN po_recv_hdr.recv_no%TYPE,
p_header_flag IN NUMBER,
p_dtl_recv_date IN DATE DEFAULT SYSDATE) AS
v_po_id po_ordr_hdr.po_id%TYPE;
v_last_updated_by po_recv_hdr.last_updated_by%TYPE;
insert_trans_row NUMBER;
/** create a variable and a cursor to select apps side uom code **/
v_bol_uom_code mtl_units_of_measure.uom_code%TYPE :=NULL;
SELECT b.UOM_CODE
FROM sy_uoms_mst a,mtl_units_of_measure b
WHERE a.um_code = v_um_code
AND a.unit_of_measure = b.unit_of_measure;
SELECT UNIT_OF_MEASURE
FROM sy_uoms_mst
WHERE um_code = v_um_code;
SELECT RCV_TRANSACTIONS_INTERFACE_S.nextval
FROM sys.dual;
SELECT RCV_INTERFACE_GROUPS_S.nextval
FROM sys.dual;
SELECT recv_no, shipvend_id,to_whse,recv_date,gross_wt,net_wt,tare_wt, substrb(waybill_no, 1, 20), /* Bug 2458366 - added waybill_no*/
bol_um,billing_currency
FROM po_recv_hdr
WHERE recv_id = vc_recv_id;
SELECT of_vendor_id
FROM po_vend_mst
WHERE vendor_id = vc_shipvend_id;
SELECT of_vendor_id
FROM po_vend_mst
WHERE vendor_id = (SELECT shipvend_id
FROM po_ordr_hdr
WHERE po_id = vc_po_id);
SELECT po_header_id, po_line_id, po_line_location_id
FROM cpg_oragems_mapping
WHERE po_id = vc_po_id AND
line_id = vc_poline_id;
SELECT vendor_site_id,freight_terms_lookup_code,currency_code,rate_type,
rate_date,rate,revision_num
FROM po_headers_all
WHERE po_header_id = vc_apps_po_header_id;
SELECT deliver_to_person_id,po_distribution_id
FROM po_distributions
WHERE po_header_id = vc_apps_po_header_id AND
po_line_id = vc_apps_po_line_id AND
line_location_id = vc_apps_po_line_location_id;
SELECT item_no
FROM ic_item_mst
WHERE item_id = vc_opm_item_id;
SELECT inventory_item_id,description
FROM mtl_system_items
WHERE segment1 = vc_opm_item_no;
/* Uday Phadtare B1785880 removed unit_price from select */
CURSOR app_po_line_cur(vc_apps_po_header_id NUMBER,
vc_apps_po_line_id NUMBER) IS
SELECT item_revision,org_id
FROM po_lines_all
WHERE po_header_id = vc_apps_po_header_id AND
po_line_id = vc_apps_po_line_id;
/* Uday Phadtare B1785880 select price_override from po_line_locations_all */
CURSOR po_line_loc_cur(vc_apps_po_header_id NUMBER,
vc_apps_po_line_id NUMBER,
vc_apps_po_line_location_id NUMBER) IS
SELECT ship_to_location_id,po_release_id,ship_to_organization_id,price_override
FROM po_line_locations_all
WHERE po_header_id = vc_apps_po_header_id AND
po_line_id = vc_apps_po_line_id AND
line_location_id = vc_apps_po_line_location_id;
SELECT revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = vc_inventory_item_id AND
organization_id = vc_ship_to_organization_id;
SELECT shipment_header_id,
shipment_line_id,
po_distribution_id,
attribute1,
interface_transaction_id
FROM rcv_transactions
WHERE transaction_id = v_transaction_id;
SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE organization_id = vc_ship_to_organization_id AND
NVL(disable_date,sysdate+1) > sysdate ;
SELECT subinventory_code
FROM mtl_item_locations
WHERE organization_id = vc_ship_to_organization_id
AND inventory_location_id = vc_inventory_location_id
AND (disable_date > sysdate or disable_date is null);
SELECT whse_code, loct_ctl
FROM ic_whse_mst
WHERE mtl_organization_id = v_ship_to_organization_id
AND delete_mark = 0;
SELECT inventory_location_id
FROM ic_loct_mst
WHERE whse_code = p_whse_code
AND inventory_location_id is not null
AND delete_mark = 0
ORDER BY inventory_location_id;
select po.segment1, lines.line_num, shipments.shipment_num
from po_headers_all po, po_lines_all lines, po_line_locations_all shipments
where po.po_header_id = vc_apps_po_header_id
and lines.po_header_id = po.po_header_id
and lines.po_line_id = vc_apps_po_line_id
and shipments.po_header_id = po.po_header_id
and shipments.po_line_id = lines.po_line_id
and shipments.line_location_id = vc_apps_po_line_location_id;
v_last_updated_by := FND_PROFILE.VALUE ('USER_ID');
IF (p_header_flag = 1) THEN /* This is a header insert */
/** MC BUG# 1554088 **/
/** replace v_bol_um with v_bol_uom_code in the insert **/
INSERT INTO rcv_headers_interface
(header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
auto_transact_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
shipment_num,
receipt_num,
vendor_id,
vendor_site_id,
ship_to_organization_id,
expected_receipt_date,
waybill_airbill_num, /* Bug 2458366 */
comments,
gross_weight,
gross_weight_uom_code,
net_weight,
net_weight_uom_code,
tar_weight,
tar_weight_uom_code,
freight_terms,
currency_code,
conversion_rate_type,
conversion_rate,
conversion_rate_date,
employee_id,
validation_flag)
VALUES
(p_header_interface_id,
p_group_id,
'PENDING',
'VENDOR',
'NEW',
'RECEIVE',
SYSDATE,
v_last_updated_by,
SYSDATE,
v_created_by,
to_char(p_header_interface_id), /* 2540428 insert p_header_interface_id instead of '1' */
v_rcv_receipt_num, /* v_recv_no */
v_of_vendor_id,
v_vendor_site_id,
v_ship_to_organization_id,
v_recv_date,
v_waybill_no, /* Bug 2458366 */
'OPM RECEIPT',
v_gross_wt,
v_bol_uom_code,
v_net_wt,
v_bol_uom_code,
v_tare_wt,
v_bol_uom_code,
v_freight_terms,
v_currency_code,
v_rate_type,
v_rate,
v_rate_date,
v_deliver_to_person_id,
'Y');
INSERT INTO rcv_transactions_interface
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
ITEM_DESCRIPTION,
ITEM_REVISION,
AUTO_TRANSACT_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
VENDOR_SITE_ID,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_REVISION_NUM,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_UNIT_PRICE,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
LOCATOR_ID,
SHIPMENT_NUM,
EXPECTED_RECEIPT_DATE,
COMMENTS,
ATTRIBUTE1,
HEADER_INTERFACE_ID,
DOCUMENT_NUM,
DOCUMENT_LINE_NUM,
DOCUMENT_SHIPMENT_LINE_NUM,
VALIDATION_FLAG)
VALUES
(new_interface_transaction_id,
new_group_id,
SYSDATE,
v_last_updated_by,
SYSDATE,
v_created_by,
v_transaction_type,
v_dtl_recv_date, /* B2007945 replaced SYSDATE with v_dtl_recv_date SYSDATE */
'PENDING',
'BATCH',
'PENDING',
v_trans_quantity,
v_recv_unit_of_measure,
v_item_id,
v_item_desc,
v_item_rev,
v_auto_transact_code,
v_shipment_header_id,
v_shipment_line_id,
v_ship_to_location_id,
'VENDOR',
v_of_vendor_id,
v_vendor_site_id,
v_ship_to_organization_id,
'PO',
v_transaction_id,
v_apps_po_header_id,
v_po_revision_num,
v_po_release_id,
v_apps_po_line_id,
v_apps_po_line_location_id,
v_unit_price,
v_currency_code,
v_rate_type,
v_rate,
v_rate_date,
v_po_distribution_id,
v_destination_type_code,
v_ship_to_location_id, /* B1766557 */
v_subinventory,
v_loct_id, /* B1685307 */
'1',
v_recv_date,
v_comment,
v_attribute1,
v_header_interface_id,
v_document_num,
v_document_line_num,
v_document_shipment_line_num,
'Y');
/* Each time a new transaction (except for adjustments ) is inserted,
insert a row in the mapping table.
*/
IF (p_header_interface_id <> 0 AND p_header_flag = 0 ) THEN
INSERT INTO gml_recv_trans_map
(recv_id, line_id, interface_transaction_id,group_id,
creation_date,created_by,last_update_date,last_updated_by,
last_update_login,organization_id,rcv_receipt_num)
VALUES
(v_recv_id, v_line_id,new_interface_transaction_id,new_group_id,
SYSDATE,v_created_by, SYSDATE,v_last_updated_by,NULL,v_ship_to_organization_id,
v_rcv_receipt_num);
G_rows_inserted := G_rows_inserted +1;
END gml_new_rcv_trans_insert;