[Home] [Help]
1384: -- : Warning! This is not the procedure which is called from
1385: -- calculate_rent. It is other overloaded post_summary.
1386: -- dd-mon-yyyy name o Created
1387: -- 21-MAY-07 Lokesh o Added rounding off for bug # 6031202 in
1388: -- pn_var_rent_summ_all
1389: --------------------------------------------------------------------------------
1390: PROCEDURE post_summary ( p_var_rent_id IN NUMBER
1391: ,p_period_id IN NUMBER
1392: ,p_line_item_id IN NUMBER
1408: grp.var_rent_id = p_vr_id AND
1409: grp.period_id = p_prd_id AND
1410: grp.grp_date_id = p_grp_id;
1411:
1412: /* Get rent and volume to store in pn_var_rent_summ_all */
1413: CURSOR summ_c( p_vr_id IN NUMBER
1414: ,p_prd_id IN NUMBER
1415: ,p_line_id IN NUMBER
1416: ,p_grp_id IN NUMBER) IS
1440: ,p_grp_id IN NUMBER) IS
1441: SELECT
1442: var_rent_summ_id
1443: FROM
1444: pn_var_rent_summ_all
1445: WHERE
1446: var_rent_id = p_vr_id AND
1447: period_id = p_prd_id AND
1448: line_item_id = p_line_id AND
1492:
1493: pnp_debug_pkg.log('inserting ...');
1494: /* to insert a new summary record */
1495: INSERT INTO
1496: pn_var_rent_summ_all
1497: (var_rent_summ_id
1498: ,var_rent_id
1499: ,period_id
1500: ,line_item_id
1536: /* update the summary record */
1537:
1538: pnp_debug_pkg.log('updating ...');
1539: UPDATE
1540: pn_var_rent_summ_all
1541: SET
1542: tot_act_vol = summ_rec.sales
1543: ,tot_ded = summ_rec.ded
1544: ,act_var_rent = round(summ_rec.rent,g_precision)
1583: -- HISTORY :
1584: --
1585: -- dd-mon-yyyy name o Created
1586: -- 21-MAY-07 Lokesh o Added rounding off for bug # 6031202 in
1587: -- pn_var_rent_summ_all
1588: --------------------------------------------------------------------------------
1589: PROCEDURE post_summary_firstyr ( p_var_rent_id IN NUMBER)
1590: IS
1591:
1592: /* exists VR summ record */
1593: CURSOR vr_summ_fy_c IS
1594: SELECT *
1595: FROM
1596: pn_var_rent_summ_all
1597: WHERE
1598: var_rent_id = p_var_rent_id AND
1599: NVL(first_yr_rent, 0) <> 0;
1600:
1605: ,tot_act_vol
1606: ,act_var_rent
1607: ,tot_ded
1608: FROM
1609: pn_var_rent_summ_all
1610: WHERE
1611: var_rent_id = p_var_rent_id AND
1612: period_id = p_period_id AND
1613: line_item_id = p_line_item_id;
1662: vr_summ_fy_rec.first_yr_rent <> NVL(vr_summ_rec.act_var_rent, 0)
1663: THEN
1664:
1665: UPDATE
1666: pn_var_rent_summ_all
1667: SET
1668: tot_act_vol = vr_summ_fy_rec.tot_act_vol
1669: ,tot_ded = vr_summ_fy_rec.tot_ded
1670: ,act_var_rent = round(vr_summ_fy_rec.first_yr_rent,g_precision)
1681:
1682:
1683: /* to insert a new summary record */
1684: INSERT INTO
1685: pn_var_rent_summ_all
1686: (var_rent_summ_id
1687: ,var_rent_id
1688: ,period_id
1689: ,line_item_id
1759: SELECT
1760: invoice_date
1761: ,NVL(SUM(first_yr_rent), 0) AS total_actual_rent
1762: FROM
1763: pn_var_rent_summ_all
1764: WHERE
1765: var_rent_id = p_vr_id AND
1766: first_yr_rent IS NOT NULL
1767: GROUP BY
2117: invoice_date
2118: ,NVL(SUM(tot_act_vol), 0) AS total_actual_sales
2119: ,NVL((SUM(act_var_rent) - NVL(SUM(first_yr_rent), 0)), 0) AS total_actual_rent
2120: FROM
2121: pn_var_rent_summ_all
2122: WHERE
2123: var_rent_id = p_vr_id AND
2124: period_id = p_prd_id AND
2125: invoice_date <= p_new_termn_date
4571: ,NVL(SUM(tot_act_vol), 0) AS total_actual_sales
4572: ,NVL(SUM(act_var_rent), 0) AS total_actual_rent
4573: ,NVL(SUM(for_var_rent), 0) AS total_forecasted_rent
4574: FROM
4575: pn_var_rent_summ_all
4576: WHERE
4577: var_rent_id = p_vr_id AND
4578: period_id = p_prd_id
4579: GROUP BY
5822: AND period_num = 1;
5823:
5824: CURSOR first_period_cur IS
5825: SELECT period_id
5826: FROM pn_var_rent_summ_all
5827: WHERE var_rent_id = vr_id
5828: AND nvl(first_yr_rent,0) <> 0
5829: AND rownum < 2;
5830:
5888: -- HISTORY :
5889: --
5890: -- 18.Sep.06 Shabda o Created
5891: -- 21-MAY-07 Lokesh o Added rounding off for bug # 6031202 in
5892: -- pn_var_rent_summ_all
5893: --------------------------------------------------------------------------------
5894: PROCEDURE post_summary_for ( p_var_rent_id IN NUMBER
5895: ,p_period_id IN NUMBER
5896: ,p_line_item_id IN NUMBER
5912: grp.var_rent_id = p_vr_id AND
5913: grp.period_id = p_prd_id AND
5914: grp.grp_date_id = p_grp_id;
5915:
5916: /* Get rent and volume to store in pn_var_rent_summ_all */
5917: CURSOR summ_c( p_vr_id IN NUMBER
5918: ,p_prd_id IN NUMBER
5919: ,p_line_id IN NUMBER
5920: ,p_grp_id IN NUMBER) IS
5938: ,p_grp_id IN NUMBER) IS
5939: SELECT
5940: var_rent_summ_id
5941: FROM
5942: pn_var_rent_summ_all
5943: WHERE
5944: var_rent_id = p_vr_id AND
5945: period_id = p_prd_id AND
5946: line_item_id = p_line_id AND
5984: IF l_vr_summ_id IS NULL THEN
5985:
5986: /* to insert a new summary record */
5987: INSERT INTO
5988: pn_var_rent_summ_all
5989: (var_rent_summ_id
5990: ,var_rent_id
5991: ,period_id
5992: ,line_item_id
6025: ELSIF l_vr_summ_id IS NOT NULL THEN
6026: /* update the summary record */
6027:
6028: UPDATE
6029: pn_var_rent_summ_all
6030: SET
6031: tot_for_vol = summ_rec.sales
6032: ,for_var_rent = round(summ_rec.rent,g_precision)
6033: ,last_update_date = SYSDATE
6072: -- HISTORY :
6073: --
6074: -- 18.Sep.06 Shabda o Created
6075: -- 21-MAY-07 Lokesh o Added rounding off for bug # 6031202 in
6076: -- pn_var_rent_summ_all
6077: --------------------------------------------------------------------------------
6078: PROCEDURE post_summary_for ( p_var_rent_id IN NUMBER
6079: ,p_period_id IN NUMBER)
6080: IS
6109:
6110: TYPE GRP_TBL IS TABLE OF grp_date_c%ROWTYPE INDEX BY BINARY_INTEGER;
6111: l_grp_t GRP_TBL;
6112:
6113: /* Get rent and volume to store in pn_var_rent_summ_all */
6114: CURSOR summ_c( p_vr_id IN NUMBER
6115: ,p_prd_id IN NUMBER
6116: ,p_line_id IN NUMBER
6117: ,p_grp_id IN NUMBER) IS
6137: var_rent_summ_id
6138: ,tot_for_vol
6139: ,for_var_rent
6140: FROM
6141: pn_var_rent_summ_all
6142: WHERE
6143: var_rent_id = p_vr_id AND
6144: period_id = p_prd_id AND
6145: line_item_id = p_line_id AND
6192: THEN
6193: pnp_debug_pkg.log('sales:'||summ_rec.sales);
6194: pnp_debug_pkg.log('rent: '||summ_rec.rent);
6195: UPDATE
6196: pn_var_rent_summ_all
6197: SET
6198: tot_for_vol = summ_rec.sales
6199: ,for_var_rent = round(summ_rec.rent,g_precision)
6200: ,last_update_date = SYSDATE
6211:
6212: /* to insert a new summary record */
6213: pnp_debug_pkg.log('Inserting a new record for var_rent_summ table');
6214: INSERT INTO
6215: pn_var_rent_summ_all
6216: (var_rent_summ_id
6217: ,var_rent_id
6218: ,period_id
6219: ,line_item_id
6293: /* Fetch the summary id and transaction id from respective tables */
6294: CURSOR summ_id_cur (p_var_rent_id NUMBER) IS
6295: SELECT /*+ leading(trx) */
6296: summ.var_rent_summ_id, trx.trx_header_id
6297: FROM pn_var_trx_headers_all trx, pn_var_rent_summ_all summ
6298: WHERE trx.grp_date_id = summ.grp_date_id
6299: AND trx.var_rent_id = p_var_rent_id
6300: AND trx.var_rent_summ_id IS NULL;
6301:
8750: CURSOR true_up_cur IS
8751: SELECT sum(NVL(act_var_rent, 0)) + sum(NVL(trueup_var_rent, 0)) - sum(NVL(first_yr_rent,0)) true_up_rent,
8752: sum(NVL(tot_act_vol, 0)) true_up_vol,
8753: sum(NVL(tot_ded, 0)) true_up_deductions
8754: FROM pn_var_rent_summ_all
8755: WHERE period_id = p_period_id;
8756:
8757: BEGIN
8758:
9703: -- fix for bug 5679847
9704: --
9705: -- dd-mon-yyyy name o Created
9706: -- 21-MAY-07 Lokesh o Added rounding off for bug # 6031202 in
9707: -- pn_var_rent_summ_all
9708: --------------------------------------------------------------------------------
9709: PROCEDURE post_summary ( p_var_rent_id IN NUMBER
9710: ,p_period_id IN NUMBER)
9711: IS
9740:
9741: TYPE GRP_TBL IS TABLE OF grp_date_c%ROWTYPE INDEX BY BINARY_INTEGER;
9742: l_grp_t GRP_TBL;
9743:
9744: /* Get rent and volume to store in pn_var_rent_summ_all */
9745: CURSOR summ_c( p_vr_id IN NUMBER
9746: ,p_prd_id IN NUMBER
9747: ,p_line_id IN NUMBER
9748: ,p_grp_id IN NUMBER) IS
9777: ,tot_ded
9778: ,act_var_rent
9779: ,first_yr_rent
9780: FROM
9781: pn_var_rent_summ_all
9782: WHERE
9783: var_rent_id = p_vr_id AND
9784: period_id = p_prd_id AND
9785: line_item_id = p_line_id AND
9843: THEN
9844:
9845: pnp_debug_pkg.log('before updation ...');
9846: UPDATE
9847: pn_var_rent_summ_all
9848: SET
9849: tot_act_vol = summ_rec.sales
9850: ,tot_ded = summ_rec.ded
9851: ,act_var_rent = round(summ_rec.rent,g_precision)
9865: pnp_debug_pkg.log('before insertion ...');
9866:
9867: /* to insert a new summary record */
9868: INSERT INTO
9869: pn_var_rent_summ_all
9870: (var_rent_summ_id
9871: ,var_rent_id
9872: ,period_id
9873: ,line_item_id
10340: CURSOR invoice_cur( p_vr_id IN NUMBER) IS
10341: SELECT
10342: invoice_date, period_id
10343: FROM
10344: pn_var_rent_summ_all
10345: WHERE
10346: var_rent_id = p_vr_id
10347: GROUP BY
10348: invoice_date, period_id
10631:
10632: CURSOR full_yr_cur IS
10633: SELECT sum(NVL(tot_act_vol, 0)) yr_vol,
10634: sum(NVL(tot_ded, 0)) yr_deductions
10635: FROM pn_var_rent_summ_all
10636: WHERE line_item_id = p_line_item_id;
10637:
10638: BEGIN
10639: