[Home] [Help]
34: ) is
35: cursor c_adj is
36: select EROSION_TYPE, EROSION_NAME, EROSION_DESC, EROSION_PER_UNIT,
37: erosion_amount
38: from qpr_pn_int_pr_adjs
39: where source_ref_line_id = p_src_ref_line_id
40: and source_ref_hdr_id = p_src_ref_hdr_id
41: and source_id = p_src_id;
42:
1366: begin
1367: select pn_int_pr_adj_id,
1368: l.ordered_qty
1369: into l_adj_id, l_ord_qty
1370: from qpr_pn_int_pr_adjs pr, qpr_pn_int_lines l
1371: where pr.source_ref_hdr_id = p_source_ref_hdr_id
1372: and pr.source_ref_line_id = p_source_ref_line_id
1373: and pr.source_id = p_src_id
1374: and pr.source_ref_line_id = l.source_ref_line_id
1376: and pr.source_id = l.source_id
1377: and erosion_type = 'COST'
1378: and rownum < 2;
1379:
1380: update qpr_pn_int_pr_adjs set erosion_per_unit = p_er_det_rec.unit_cost,
1381: erosion_amount = p_er_det_rec.unit_cost * l_ord_qty
1382: where pn_int_pr_adj_id = l_adj_id;
1383:
1384: log_debug('updated cost: unit cost' || p_er_det_rec.unit_cost);
1388: end;
1389: end if;
1390:
1391: -- inserting oninvoice modifier: QPR_WHATIF --
1392: delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_source_ref_hdr_id
1393: and source_ref_line_id = p_source_ref_line_id
1394: and source_id = p_src_id
1395: and erosion_type = 'ONINVOICE' and erosion_name = 'QPR_WHATIF';
1396:
1402: when no_data_found then
1403: l_er_name := 'What-If Analysis';
1404: end;
1405:
1406: insert into qpr_pn_int_pr_adjs(PN_INT_PR_ADJ_ID,
1407: SOURCE_REF_HDR_ID,
1408: SOURCE_REF_LINE_ID,
1409: SOURCE_ID,
1410: EROSION_TYPE,
1416: CREATED_BY,
1417: LAST_UPDATE_DATE,
1418: LAST_UPDATED_BY,
1419: LAST_UPDATE_LOGIN)
1420: values(qpr_pn_int_pr_adjs_s.nextval,
1421: p_source_ref_hdr_id,
1422: p_source_ref_line_id,
1423: p_src_id,
1424: 'ONINVOICE',
1433:
1434: log_debug('Inserted oninvoice modifier QPR_WHATIF for use in whatif');
1435:
1436: -- Insert offinvoice modifiers ----
1437: delete qpr_pn_int_pr_adjs where source_ref_hdr_id = p_source_ref_hdr_id
1438: and source_ref_line_id = p_source_ref_line_id
1439: and source_id = p_src_id
1440: and erosion_type = 'OFFINVOICE';
1441:
1449: t_er_val, t_tot_er;
1450: close c_offadj;
1451:
1452: forall i in t_er_name.first..t_er_name.last
1453: insert into qpr_pn_int_pr_adjs(PN_INT_PR_ADJ_ID,
1454: SOURCE_REF_HDR_ID,
1455: SOURCE_REF_LINE_ID,
1456: SOURCE_ID,
1457: EROSION_TYPE,
1463: CREATED_BY,
1464: LAST_UPDATE_DATE,
1465: LAST_UPDATED_BY,
1466: LAST_UPDATE_LOGIN)
1467: values(qpr_pn_int_pr_adjs_s.nextval,
1468: p_source_ref_hdr_id,
1469: p_source_ref_line_id,
1470: p_src_id,
1471: t_er_typ(i),
1506: select * from qpr_pn_pr_types order by sequence_no;
1507:
1508: cursor c_adjs(p_erosion_type varchar2) is
1509: select nvl(sum(erosion_per_unit),0) unit_erosion
1510: from qpr_pn_int_pr_adjs
1511: where source_ref_line_id = p_src_ref_line_id
1512: and source_ref_hdr_id = p_src_ref_hdr_id
1513: and source_id = p_src_id
1514: and erosion_type = p_erosion_type;
1864:
1865: delete qpr_pn_int_lines where source_ref_hdr_id = t_src_hdr_id(i)
1866: and source_id = t_src_id(i);
1867:
1868: delete qpr_pn_int_pr_adjs where source_ref_hdr_id = t_src_hdr_id(i)
1869: and source_id = t_src_id(i);
1870:
1871: commit;
1872: end if;
1972:
1973: delete qpr_pn_int_lines where source_ref_hdr_id = l_src_hdr_id
1974: and source_id = p_source_id;
1975:
1976: delete qpr_pn_int_pr_adjs where source_ref_hdr_id = l_src_hdr_id
1977: and source_id = p_source_id;
1978:
1979: end if;
1980: end;