[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE mr_insert_header ;
SELECT user_name
FROM fnd_user
WHERE (C_fBuyer_Plnr is NULL OR user_name >= C_fBuyer_Plnr)
AND (C_tBuyer_Plnr is NULL OR user_name <= C_tBuyer_Plnr);
SELECT user_id
FROM fnd_user
WHERE user_name = C_Buyer_Plnr ;
select min(user_name) INTO X_fBuyer_Plnr from fnd_user;
select max(user_name) INTO X_tBuyer_Plnr from fnd_user;
G_planning_tab.delete;
mr_insert_header;
| PROCEDURE NAME mr_insert_header |
| |
| DESCRIPTION Procedure to insert data into ps_matl_hdr |
| This Procedure fetches data for the Header Table by |
| building the Where condition based on the User and the|
| Planning Classes and then inserts into the Header |
| Table by creating a record group |
| |
| MODIFICATION HISTORY |
| 12/31/02 Sridhar Gidugu ----- created |
| |
+============================================================================*/
PROCEDURE mr_insert_header IS
X_where VARCHAR2(5000) := NULL ;
X_where := 'SELECT i.planning_class,i.item_id '
||' FROM ps_oper_pcl c, ps_plng_cls p, ic_item_mst i, '
||' fnd_user f '
||' WHERE c.delete_mark=0 ' ;
X_where:= X_where||' AND i.item_id in (select distinct item_id '
||' FROM mr_tran_tbl WHERE mrp_id= to_char(:6) )' ;
SELECT gem5_matl_rep_id_s.nextval INTO X_rep_id FROM dual;
INSERT INTO ps_matl_hdr (matl_rep_id,planning_class,item_id)
VALUES(X_rep_id,X_planning_class,X_item_id);
END mr_insert_header; /***** END PROCEDURE********************/
G_doc_tab.delete;
X_select VARCHAR2(3000);
X_select := ' SELECT mr.doc_type doc_type,mr.trans_date trans_date, '||
' mr.orgn_code orgn_code,mr.doc_id doc_id, '||
' mr.trans_qty trans_qty, '||
' null cust_vend, mr.line_no line_no, mr.whse_code whse_code'||
' FROM mr_tran_tbl mr'||
' WHERE mrp_id = TO_CHAR(:1) ' ||
' AND mr.item_id = TO_CHAR(:2) ' ||
' AND mr.whse_code in ('|| G_whse_list ||')'||
' AND mr.doc_type in ('||''''||'PROD'||''''||','||''''||'FPO'||''''||',
'||''''||'PORD'||''''||','||''''||'PREQ'||''''||',
'||''''||'OMSO'||''''||','||''''||'OPSO'||''''||',
'||''''||'PPUR'||''''||','||''''||'PPRD'||''''||',
'||''''||'FCST'||''''||','||''''||'PTRN'||''''||',
'||''''||'LEXP'||''''||','||''''||'PRCV'||''''||','||''''||'SHMT'||''''||',
'||''''||'PBPR'||''''||','||''''||'XFER'||''''||','||''''||'PBPO'||''''||')'||
' ORDER BY 2 ASC, 5 DESC';
dbms_sql.parse(X_doc, X_select,dbms_sql.NATIVE);
SELECT sysdate into X_date from dual;
/* Insert the Transaction Data into mr_ubkt_dtl table
for Report to Process and show the data on the screen */
INSERT INTO mr_ubkt_dtl(matl_rep_id,
item_id,
planning_class,
whse_code,
start_balance,
past_due,
trans_date,
doc_type,
orgn_code,
doc_id,
doc_no,
trans_qty,
balance,
critical_ind,
cust_vend
)
VALUES(G_matl_rep_id,
V_item_id,
V_planning_class,
G_doc_tab(X_i).whse_code,
G_start_balance,
X_pastdue,
G_doc_tab(X_i).trans_date,
G_doc_tab(X_i).doc_type,
G_doc_tab(X_i).orgn_code,
-- nvl(G_doc_tab(i).doc_id,0),
nvl(G_doc_id,0),
G_doc_no,
G_doc_tab(X_i).trans_qty,
G_balance1,
G_c_ind,
--Begin Bug#2131275 P.Raghu
--G_cust_vend value is inserted instead of NULL.
--G_doc_tab(X_i).cust_vend
G_cust_vend
--End Bug#2131275
);
/* Insert data complete */
G_start_balance := 0.00;
SELECT sysdate into X_date from dual;
/* Insert the Transaction Data into mr_ubkt_dtl table
for Report to Process and show the data on the screen */
INSERT INTO mr_ubkt_dtl(matl_rep_id,
item_id,
planning_class,
whse_code,
start_balance,
past_due,
trans_date,
doc_type,
orgn_code,
doc_id,
doc_no,
trans_qty,
balance,
critical_ind,
cust_vend
)
VALUES(G_matl_rep_id,
V_item_id,
V_planning_class,
G_doc_tab(X_i).whse_code,
G_start_balance,
X_pastdue,
G_doc_tab(X_i).trans_date,
G_doc_tab(X_i).doc_type,
G_doc_tab(X_i).orgn_code,
-- nvl(G_doc_tab(i).doc_id,0),
nvl(G_doc_id,0),
G_doc_no,
G_doc_tab(X_i).trans_qty,
G_balance1,
G_c_ind,
--Begin Bug#2131275 P.Raghu
--G_cust_vend value is inserted instead of NULL.
--G_doc_tab(X_i).cust_vend
G_cust_vend
--End Bug#2131275
);
/* Insert data complete */
G_start_balance := 0.00;
G_doc_tab.delete;
/* if there are no transactions then that item row is deleted from
header table. */
IF X_i = 0 THEN
DELETE FROM ps_matl_hdr
WHERE item_id = V_item_id
AND matl_rep_id = G_matl_rep_id;
SELECT whse_item_id
FROM ic_item_mst
WHERE item_id = V_item_id;
SELECT MIN(whse_code) INTO X_fwhse_code FROM ic_whse_mst;
SELECT MAX(whse_code) INTO X_twhse_code FROM ic_whse_mst;
/* Define Cursor per whse security to select the whse code */
IF nvl(G_whse_security,'N') = 'N' THEN
--Bug 3168907 Added ps_whse_eff whs to fetch whse_item_id
OPEN Cur_matl_act for
SELECT distinct trn.whse_code, whs.whse_item_id
FROM mr_tran_tbl trn, ps_schd_dtl sch, ps_whse_eff whs
WHERE sch.schedule_id = G_schedule_id
AND trn.mrp_id = G_mrp_id
AND item_id = V_item_id
AND whs.whse_code = trn.whse_code
AND whs.plant_code = sch.orgn_code
AND (whs.whse_code >= X_fwhse_code
OR X_fwhse_code IS NULL)
AND (whs.whse_code <= X_twhse_code
OR X_twhse_code IS NULL)
ORDER BY 1;
SELECT distinct trn.whse_code, whs.whse_item_id
FROM mr_tran_tbl trn, ps_schd_dtl sch, sy_orgn_usr org, ps_whse_eff whs
WHERE sch.orgn_code = org.orgn_code
and sch.schedule_id = G_schedule_id
and mrp_id = G_mrp_id
and item_id = V_item_id
and org.user_id = G_Buyer_plnr_id
and whs.plant_code = sch.orgn_code
and whs.whse_code = trn.whse_code
and (whs.whse_code >= X_fwhse_code
or X_fwhse_code IS NULL)
and (whs.whse_code <= X_twhse_code
or X_twhse_code IS NULL)
ORDER BY 1 ;
X_select1 VARCHAR2(2000) := NULL;
X_select1 :='SELECT sum(trans_qty) total'||
' FROM mr_tran_tbl mr'||
' WHERE mrp_id= to_char(:1) AND item_id = to_char(:2) ' ||
' AND whse_code in (' || G_whse_list || ') ' ||
' AND doc_type='||''''||'BAL'||''''||
' group by mr.doc_type';
dbms_sql.parse (cur_balance, X_select1,dbms_sql.NATIVE);
SELECT safety_stock
FROM ic_whse_inv
WHERE item_id= C_item_id
AND whse_code is NULL and delete_mark=0;
X_select1 VARCHAR2(2000) := NULL ;
X_select1 :='SELECT sum(safety_stock) total_ss,count(*) no_ss'||
' FROM ic_whse_inv'||
' WHERE item_id= to_char(:1) ' ||
' AND whse_code in ('|| G_whse_list ||') and delete_mark=0';
dbms_sql.parse (cur_sstock, X_select1,dbms_sql.NATIVE);
# to select vendor_name and suggested_vendor_name instead of
# segment1 value for Customer/Vendor respectively.
# Sastry 04/01/2004 - B3482123 - Replaced po_headers_all table with
# po_po_supply_view so that releases are shown for 'PORD' doc_type.
#############################################################################*/
PROCEDURE mr_cleanup_details IS
CURSOR Cur_custno IS
SELECT cs.cust_no cust_no
FROM op_cust_mst cs, op_ordr_dtl dt
WHERE dt.bol_id = G_doc_id
AND dt.shipcust_id = cs.cust_id;
SELECT unique mtt.orgn_code, po.po_number, SUBSTRB(vn.vendor_name,1,32)
FROM po_po_supply_view po,
po_vendors vn,
mr_tran_tbl mtt
WHERE po.po_line_location_id = mtt.doc_id
AND mtt.mrp_id = G_mrp_id
AND mtt.doc_id = G_doc_id
AND mtt.line_no = G_line_no
AND mtt.doc_type = 'PORD'
AND vn.vendor_id (+) = po.vendor_id ;
SELECT unique mtt.orgn_code, prh.receipt_num, SUBSTRB(vn.vendor_name,1,32)
FROM rcv_shipment_headers prh,
po_vendors vn,
mr_tran_tbl mtt
WHERE prh.shipment_header_id = G_doc_id
AND mtt.mrp_id = G_mrp_id
AND mtt.line_no = G_line_no
AND mtt.doc_id = G_doc_id
AND mtt.doc_type in ('PRCV','SHMT')
AND vn.vendor_id (+) = prh.vendor_id
UNION ALL
SELECT unique mtt.orgn_code, prh.segment1, SUBSTRB(vn.vendor_name,1,32)
FROM po_headers_all prh,
po_vendors vn,
mr_tran_tbl mtt
WHERE prh.po_header_id = G_doc_id
AND mtt.mrp_id = G_mrp_id
AND mtt.line_no = G_line_no
AND mtt.doc_id = G_doc_id
AND mtt.doc_type = 'PRCV'
AND vn.vendor_id (+) = prh.vendor_id ;
SELECT op.orgn_code, op.order_no,cs.cust_no
FROM op_ordr_hdr op, op_cust_mst cs
WHERE op.order_id = G_doc_id
AND op.shipcust_id = cs.cust_id;
SELECT op.orgn_code, op.bol_no
FROM op_bill_lad op
WHERE op.bol_id = G_doc_id;
SELECT gbh.plant_code, gbh.batch_no
FROM gme_batch_header gbh
WHERE gbh.batch_id = G_doc_id
AND gbh.delete_mark = 0;
SELECT ic.orgn_code,ic.transfer_no
FROM ic_xfer_mst ic
WHERE ic.transfer_id = G_doc_id;
SELECT oh.order_number, sold_to_org.customer_number
FROM oe_order_headers_all oh,
oe_sold_to_orgs_v sold_to_org
WHERE oh.header_id = G_doc_id
AND oh.sold_to_org_id = sold_to_org.organization_id(+) ;
SELECT bh.orgn_code, bh.bpo_no,bd.line_no
FROM po_bpos_dtl bd, po_bpos_hdr bh
WHERE bd.line_id = G_doc_id
AND bd.bpo_id = bh.bpo_id;
SELECT unique mtt.orgn_code, prh.segment1, SUBSTRB(prl.suggested_vendor_name,1,32)
FROM po_requisition_headers prh,
po_requisition_lines prl,
po_vendors vn,
mr_tran_tbl mtt
WHERE prl.requisition_header_id = G_doc_id
AND prh.requisition_header_id = prl.requisition_header_id
AND mtt.mrp_id = G_mrp_id
AND mtt.line_no = G_line_no
AND mtt.doc_id = G_doc_id
AND mtt.doc_type = 'PREQ'
AND vn.vendor_id (+) = prl.vendor_id ;
select gem5_mrp_doc_id_s.nextval into G_doc_id
from dual;