[Home] [Help]
320: END IF;
321:
322: IF (G_mrp_id IS NOT NULL) THEN
323: X_where:= X_where||' AND i.item_id in (select distinct item_id '
324: ||' FROM mr_tran_tbl WHERE mrp_id= to_char(:6) )' ;
325: END IF;
326:
327: cur_planning := dbms_sql.open_cursor;
328: dbms_sql.parse (cur_planning, X_where,dbms_sql.NATIVE);
458: | |
459: | PROCEDURE NAME mr_data_retrieval |
460: | |
461: | DESCRIPTION Procedure to Retrieve the Data for Unbucketed material|
462: | activity and for each item from mr_tran_tbl based on |
463: | mrp_id and warehouse list |
464: | |
465: | MODIFICATION HISTORY |
466: | 12/31/02 Sridhar Gidugu ----- created |
507: X_select := ' SELECT mr.doc_type doc_type,mr.trans_date trans_date, '||
508: ' mr.orgn_code orgn_code,mr.doc_id doc_id, '||
509: ' mr.trans_qty trans_qty, '||
510: ' null cust_vend, mr.line_no line_no, mr.whse_code whse_code'||
511: ' FROM mr_tran_tbl mr'||
512: ' WHERE mrp_id = TO_CHAR(:1) ' ||
513: ' AND mr.item_id = TO_CHAR(:2) ' ||
514: ' AND mr.whse_code in ('|| G_whse_list ||')'||
515: ' AND mr.doc_type in ('||''''||'PROD'||''''||','||''''||'FPO'||''''||',
791: IF nvl(G_whse_security,'N') = 'N' THEN
792: --Bug 3168907 Added ps_whse_eff whs to fetch whse_item_id
793: OPEN Cur_matl_act for
794: SELECT distinct trn.whse_code, whs.whse_item_id
795: FROM mr_tran_tbl trn, ps_schd_dtl sch, ps_whse_eff whs
796: WHERE sch.schedule_id = G_schedule_id
797: AND trn.mrp_id = G_mrp_id
798: AND item_id = V_item_id
799: AND whs.whse_code = trn.whse_code
807: ELSE
808: --Bug 3168907 Added ps_whse_eff whs to fetch whse_item_id
809: OPEN Cur_matl_act for
810: SELECT distinct trn.whse_code, whs.whse_item_id
811: FROM mr_tran_tbl trn, ps_schd_dtl sch, sy_orgn_usr org, ps_whse_eff whs
812: WHERE sch.orgn_code = org.orgn_code
813: and sch.schedule_id = G_schedule_id
814: and mrp_id = G_mrp_id
815: and item_id = V_item_id
872: /*============================================================================+
873: | |
874: | PROCEDURE NAME mr_get_balance |
875: | |
876: | DESCRIPTION Procedure to get the Initial balance from mr_tran_tbl |
877: | for that particular Item |
878: | |
879: | MODIFICATION HISTORY |
880: | 01/01/03 Sridhar Gidugu ----- created |
887: cur_balance NUMBER;
888: X_row_count NUMBER(5);
889: BEGIN
890: X_select1 :='SELECT sum(trans_qty) total'||
891: ' FROM mr_tran_tbl mr'||
892: ' WHERE mrp_id= to_char(:1) AND item_id = to_char(:2) ' ||
893: ' AND whse_code in (' || G_whse_list || ') ' ||
894: ' AND doc_type='||''''||'BAL'||''''||
895: ' group by mr.doc_type';
1038: CURSOR Cur_vend_dtl IS
1039: SELECT unique mtt.orgn_code, po.po_number, SUBSTRB(vn.vendor_name,1,32)
1040: FROM po_po_supply_view po,
1041: po_vendors vn,
1042: mr_tran_tbl mtt
1043: WHERE po.po_line_location_id = mtt.doc_id
1044: AND mtt.mrp_id = G_mrp_id
1045: AND mtt.doc_id = G_doc_id
1046: AND mtt.line_no = G_line_no
1053: CURSOR Cur_prcv_dtl IS
1054: SELECT unique mtt.orgn_code, prh.receipt_num, SUBSTRB(vn.vendor_name,1,32)
1055: FROM rcv_shipment_headers prh,
1056: po_vendors vn,
1057: mr_tran_tbl mtt
1058: WHERE prh.shipment_header_id = G_doc_id
1059: AND mtt.mrp_id = G_mrp_id
1060: AND mtt.line_no = G_line_no
1061: AND mtt.doc_id = G_doc_id
1064: UNION ALL
1065: SELECT unique mtt.orgn_code, prh.segment1, SUBSTRB(vn.vendor_name,1,32)
1066: FROM po_headers_all prh,
1067: po_vendors vn,
1068: mr_tran_tbl mtt
1069: WHERE prh.po_header_id = G_doc_id
1070: AND mtt.mrp_id = G_mrp_id
1071: AND mtt.line_no = G_line_no
1072: AND mtt.doc_id = G_doc_id
1117: SELECT unique mtt.orgn_code, prh.segment1, SUBSTRB(prl.suggested_vendor_name,1,32)
1118: FROM po_requisition_headers prh,
1119: po_requisition_lines prl,
1120: po_vendors vn,
1121: mr_tran_tbl mtt
1122: WHERE prl.requisition_header_id = G_doc_id
1123: AND prh.requisition_header_id = prl.requisition_header_id
1124: AND mtt.mrp_id = G_mrp_id
1125: AND mtt.line_no = G_line_no