[Home] [Help]
49: marker rows using IC_LOCT_INV
50: */
51: edw_log.put_line ('Before creating New Period begin marker row for Company :'||cldr_rec.co_code);
52:
53: INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
54: ,ORGN_CODE
55: ,WHSE_CODE
56: ,LOCATION
57: ,AVG_ONH_QTY
93: ,cldr_rec.start_date TRX_DATE
94: ,0 WIP_COMP_QTY
95: ,0 WIP_ISSUE_QTY
96: ,0
97: FROM opi_pmi_inv_daily_stat_sum
98: WHERE co_code = cldr_rec.co_code
99: AND trx_date = cldr_rec.last_end_Date);
100:
101: IF sql%rowcount = 0 THEN
98: WHERE co_code = cldr_rec.co_code
99: AND trx_date = cldr_rec.last_end_Date);
100:
101: IF sql%rowcount = 0 THEN
102: INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
103: ,ORGN_CODE
104: ,WHSE_CODE
105: ,LOCATION
106: ,AVG_ONH_QTY
154: edw_log.put_line ('Before creating New Period end marker row for Company :'||cldr_rec.co_code );
155:
156:
157:
158: INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
159: ,ORGN_CODE
160: ,WHSE_CODE
161: ,LOCATION
162: ,AVG_ONH_QTY
205: WHERE co_code = cldr_rec.co_code
206: AND loct.whse_code = whs.whse_code
207: AND whs.orgn_code = org.orgn_code);
208: ELSE
209: INSERT INTO opi_pmi_inv_daily_stat_sum (CO_CODE
210: ,ORGN_CODE
211: ,WHSE_CODE
212: ,LOCATION
213: ,AVG_ONH_QTY
249: ,cldr_rec.end_date TRX_DATE
250: ,0 WIP_COMP_QTY
251: ,0 WIP_ISSUE_QTY
252: ,1
253: FROM opi_pmi_inv_daily_stat_sum
254: WHERE co_code = cldr_rec.co_code
255: AND trx_date = cldr_rec.last_end_Date);
256: END IF;
257:
501: ,sm.WIP_COMP_QTY
502: ,sm.WIP_ISSUE_QTY
503: ,sm.period_flag
504: ,'UPDATE' OPERATION_CODE
505: from opi_pmi_inv_daily_stat_sum sm, opi_pmi_ids_idnt idnt
506: where idnt.co_code = sm.co_code AND
507: idnt.orgn_code = sm.orgn_code AND
508: idnt.whse_code = sm.whse_code AND
509: idnt.location = sm.location AND
540: AND trx_Date >= rec_summary.trans_date
541: order by trx_date ASC;
542: CURSOR get_balance_row IS
543: select trx_Date,end_onh_qty,beg_onh_qty
544: from opi_pmi_inv_daily_stat_sum
545: where co_code = rec_summary.co_code
546: AND whse_code = rec_summary.whse_code
547: AND orgn_code = rec_summary.orgn_code
548: AND Location = rec_summary.location
1051: l_insert_row_cnt number;
1052: BEGIN
1053: /* Insert all new rows from work table into summary table using operation code = 'INSERT'*/
1054:
1055: insert into opi_pmi_inv_daily_stat_sum (CO_CODE
1056: ,ORGN_CODE
1057: ,WHSE_CODE
1058: ,LOCATION
1059: ,AVG_ONH_QTY
1104: -- dbms_output.put_line('Rows inserted '||l_insert_row_cnt);
1105: commit;
1106: /* update summary table using rows marked with operation code = 'UPDATE' in work table */
1107:
1108: UPDATE opi_pmi_inv_daily_stat_sum sm
1109: SET (
1110: AVG_ONH_QTY
1111: ,BEG_ONH_QTY
1112: ,CREATION_DATE
1242: ,sm.WIP_COMP_QTY
1243: ,sm.WIP_ISSUE_QTY
1244: ,sm.PERIOD_STATUS
1245: ,sm.DATA_PUSHED_IND
1246: FROM OPI_PMI_INV_DAILY_STAT_SUM sm
1247: WHERE NVL(PERIOD_STATUS,0) <> 2 );
1248: /* Delete rows from summary table which are moved to work table for costing.
1249: later we can insert these rows from work table back to summary table since
1250: delete and insert of mass transactions are faster than update */
1247: WHERE NVL(PERIOD_STATUS,0) <> 2 );
1248: /* Delete rows from summary table which are moved to work table for costing.
1249: later we can insert these rows from work table back to summary table since
1250: delete and insert of mass transactions are faster than update */
1251: delete OPI_PMI_INV_DAILY_STAT_SUM
1252: WHERE NVL(PERIOD_STATUS,0) <> 2 ;
1253:
1254: /* Insert parameter to costing procedure in opi_pmi_cost_param_gtmp.
1255: Parameter are Organization code, warehouse code, item id, transaction date
1268:
1269: /* Move data from temporary summary table to actual summary table and calculate the values
1270: using cost data inserted into opi_pmi_cost_result_gtmp table by costing procedure */
1271:
1272: INSERT INTO OPI_PMI_INV_DAILY_STAT_SUM (CO_CODE
1273: ,ORGN_CODE
1274: ,WHSE_CODE
1275: ,LOCATION
1276: ,ITEM_ID