[Home] [Help]
13: /*----------------------- Private Routine prototypes -----------------------*/
14:
15: PROCEDURE Create_Local_Dist_Pvt
16: (
17: p_account_line_id IN psb_ws_lines.account_line_id%TYPE ,
18: p_new_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
19: p_new_position_line_id IN psb_ws_lines_positions.position_line_id%TYPE ,
20: p_return_status OUT NOCOPY VARCHAR2
21: ) ;
15: PROCEDURE Create_Local_Dist_Pvt
16: (
17: p_account_line_id IN psb_ws_lines.account_line_id%TYPE ,
18: p_new_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
19: p_new_position_line_id IN psb_ws_lines_positions.position_line_id%TYPE ,
20: p_return_status OUT NOCOPY VARCHAR2
21: ) ;
22:
23: PROCEDURE Insert_WS_Lines_Pvt
21: ) ;
22:
23: PROCEDURE Insert_WS_Lines_Pvt
24: (
25: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
26: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
22:
23: PROCEDURE Insert_WS_Lines_Pvt
24: (
25: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
26: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
23: PROCEDURE Insert_WS_Lines_Pvt
24: (
25: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
26: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
24: (
25: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
26: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
32: p_created_by IN psb_ws_lines.created_by%TYPE,
25: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
26: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
32: p_created_by IN psb_ws_lines.created_by%TYPE,
33: p_creation_date IN psb_ws_lines.creation_date%TYPE,
26: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
32: p_created_by IN psb_ws_lines.created_by%TYPE,
33: p_creation_date IN psb_ws_lines.creation_date%TYPE,
34: p_return_status OUT NOCOPY VARCHAR2
27: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
32: p_created_by IN psb_ws_lines.created_by%TYPE,
33: p_creation_date IN psb_ws_lines.creation_date%TYPE,
34: p_return_status OUT NOCOPY VARCHAR2
35: ) ;
28: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
32: p_created_by IN psb_ws_lines.created_by%TYPE,
33: p_creation_date IN psb_ws_lines.creation_date%TYPE,
34: p_return_status OUT NOCOPY VARCHAR2
35: ) ;
36:
29: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
30: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
31: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
32: p_created_by IN psb_ws_lines.created_by%TYPE,
33: p_creation_date IN psb_ws_lines.creation_date%TYPE,
34: p_return_status OUT NOCOPY VARCHAR2
35: ) ;
36:
37: PROCEDURE Delete_Worksheet_Pvt
750: (
751: SELECT lines.* ,
752: accts.code_combination_id ,
753: accts.budget_group_id
754: FROM psb_ws_lines lines ,
755: psb_ws_account_lines accts
756: WHERE lines.worksheet_id = p_worksheet_id
757: AND lines.account_line_id = accts.account_line_id
758: AND ( l_service_package_count = 0
790: -- At least one line should get created for the worksheet.
791: l_lines_added := l_lines_added + 1 ;
792:
793: --
794: -- Put the CCID in the psb_ws_lines table for the new worksheet.
795: --
796: Insert_WS_Lines_Pvt
797: (
798: p_worksheet_id => l_new_worksheet_id,
827: --
828: END LOOP;
829:
830: --
831: -- Maintain psb_ws_lines_positions table if worksheet contains positions.
832: --
833: IF l_budget_by_position = 'Y' THEN
834:
835: --
833: IF l_budget_by_position = 'Y' THEN
834:
835: --
836: -- This loop gets all the position for the given worksheet. To maintain
837: -- psb_ws_lines_positions matrix table for the new worksheet, we will
838: -- consider all the positions falling in the given table of position sets.
839: --
840: FOR l_lines_pos_rec IN
841: (
840: FOR l_lines_pos_rec IN
841: (
842: SELECT pos_lines.* ,
843: pos.position_id
844: FROM psb_ws_lines_positions pos_lines ,
845: psb_ws_position_lines pos
846: WHERE pos_lines.worksheet_id = p_worksheet_id
847: AND pos.position_line_id = pos_lines.position_line_id
848: AND (
888: -- At least one line should get created for the worksheet.
889: l_lines_added := l_lines_added + 1 ;
890:
891: --
892: -- Put the position_line in the psb_ws_lines_position table.
893: --
894: PSB_WS_Pos_Pvt.Create_Position_Matrix
895: (
896: p_api_version => 1.0 ,
914: END IF;
915: --
916:
917: --
918: -- Maintain psb_ws_lines matrix for the account lines related to
919: -- current position_line_id (l_lines_pos_rec.position_line_id).
920: --
921: FOR l_lines_rec IN
922: (
920: --
921: FOR l_lines_rec IN
922: (
923: SELECT lines.*
924: FROM psb_ws_lines lines,
925: psb_ws_account_lines accts
926: WHERE accts.position_line_id = l_lines_pos_rec.position_line_id
927: AND lines.worksheet_id = p_worksheet_id
928: AND lines.account_line_id = accts.account_line_id
1232: END IF ;
1233:
1234: --
1235: -- This LOOP gets all the account_line_id for the new worksheet which will
1236: -- be used to maintain psb_ws_lines table.
1237: --
1238: FOR l_lines_rec IN
1239: (
1240: SELECT lines.*
1237: --
1238: FOR l_lines_rec IN
1239: (
1240: SELECT lines.*
1241: FROM psb_ws_lines lines ,
1242: psb_ws_account_lines accts
1243: WHERE lines.worksheet_id = p_worksheet_id
1244: AND lines.account_line_id = accts.account_line_id
1245: /*For Bug No : 2236283 Start*/
1272: )
1273: LOOP
1274:
1275: --
1276: -- Put the CCID in the psb_ws_lines table for the new worksheet.
1277: --
1278: Insert_WS_Lines_Pvt
1279: (
1280: p_worksheet_id => l_new_worksheet_id,
1298: END LOOP;
1299: --
1300:
1301: --
1302: -- Maintain psb_ws_lines_positions table if worksheet contains positions.
1303: -- ( This also means the worksheet includes position budgeting. )
1304: --
1305: IF l_budget_by_position = 'Y' THEN
1306:
1305: IF l_budget_by_position = 'Y' THEN
1306:
1307: --
1308: -- This loop gets all the position_line_id for the new worksheet which will
1309: -- be used to maintain psb_ws_lines_positions table.
1310: --
1311: FOR l_lines_rec IN
1312: (
1313: SELECT lines.*
1310: --
1311: FOR l_lines_rec IN
1312: (
1313: SELECT lines.*
1314: FROM psb_ws_lines_positions lines ,
1315: psb_ws_position_lines pos
1316: WHERE lines.worksheet_id = p_worksheet_id
1317: AND lines.position_line_id = pos.position_line_id
1318: /*For Bug No : 2236283 Start*/
1361: /*For Bug No : 2236283 End*/
1362: )
1363: LOOP
1364: --
1365: -- Put the position_line_id in the psb_ws_lines_positions table for
1366: -- the new worksheet.
1367: --
1368: PSB_WS_Pos_Pvt.Create_Position_Matrix
1369: (
1471: l_budget_by_position psb_worksheets.budget_by_position%TYPE ;
1472: l_worksheet_description psb_worksheets.description%TYPE ;
1473: l_main_budget_group_name psb_budget_groups.name%TYPE ;
1474: l_new_worksheet_id psb_worksheets.worksheet_id%TYPE ;
1475: l_new_position_line_id psb_ws_lines_positions.position_line_id%TYPE ;
1476: l_new_fte_line_id psb_ws_fte_lines.fte_line_id%TYPE ;
1477: l_new_element_line_id psb_ws_element_lines.element_line_id%TYPE ;
1478: l_new_position_assignment_id
1479: psb_position_assignments.position_assignment_id%TYPE ;
1621: END IF;
1622:
1623: --
1624: -- Get account_line related info for the current worksheet to be copied in
1625: -- psb_ws_lines and psb_ws_account_lines table for the target worksheet.
1626: -- The positon related account lines will be updated by position phase.
1627: --
1628: FOR l_lines_accts_rec IN
1629: (
1627: --
1628: FOR l_lines_accts_rec IN
1629: (
1630: SELECT accts.*
1631: FROM psb_ws_lines lines ,
1632: psb_ws_account_lines accts
1633: WHERE lines.worksheet_id = p_worksheet_id
1634: AND lines.account_line_id = accts.account_line_id
1635: AND accts.position_line_id IS NULL
1635: AND accts.position_line_id IS NULL
1636: )
1637: LOOP
1638: --
1639: -- Create records in psb_ws_lines and psb_ws_account_lines for the
1640: -- new worksheet.
1641: --
1642: PSB_WS_Ops_Pvt.Create_Local_Dist_Pvt
1643: (
1671:
1672: FOR l_lines_pos_rec IN
1673: (
1674: SELECT positions.*
1675: FROM psb_ws_lines_positions lines ,
1676: psb_ws_position_lines positions
1677: WHERE lines.worksheet_id = p_worksheet_id
1678: AND lines.position_line_id = positions.position_line_id
1679: )
1679: )
1680: LOOP
1681:
1682: --
1683: -- API creates records in psb_ws_lines_positions and
1684: -- psb_ws_position_lines for the new worksheet.
1685: --
1686: PSB_WS_Pos_Pvt.Create_Position_Lines
1687: (
1747: WHERE position_line_id = l_lines_pos_rec.position_line_id
1748: AND account_line_id IN
1749: (
1750: SELECT account_line_id
1751: FROM psb_ws_lines
1752: WHERE worksheet_id = l_new_worksheet_id
1753: ) ;
1754: */
1755:
2065: l_target_freeze_flag psb_worksheets.freeze_flag%TYPE ;
2066: l_target_local_copy_flag psb_worksheets.local_copy_flag%TYPE ;
2067: l_target_current_stage_seq psb_worksheets.current_stage_seq%TYPE ;
2068: l_target_account_line_id psb_ws_account_lines.account_line_id%TYPE ;
2069: l_target_position_line_id psb_ws_lines_positions.position_line_id%TYPE;
2070: l_target_position_id psb_ws_position_lines.position_id%TYPE;
2071: --
2072: l_new_position_line_id psb_ws_lines_positions.position_line_id%TYPE;
2073: l_new_fte_line_id psb_ws_fte_lines.fte_line_id%TYPE ;
2068: l_target_account_line_id psb_ws_account_lines.account_line_id%TYPE ;
2069: l_target_position_line_id psb_ws_lines_positions.position_line_id%TYPE;
2070: l_target_position_id psb_ws_position_lines.position_id%TYPE;
2071: --
2072: l_new_position_line_id psb_ws_lines_positions.position_line_id%TYPE;
2073: l_new_fte_line_id psb_ws_fte_lines.fte_line_id%TYPE ;
2074: l_new_element_line_id psb_ws_element_lines.element_line_id%TYPE ;
2075: l_new_position_assignment_id
2076: psb_position_assignments.position_assignment_id%TYPE ;
2201: --added the last three filters
2202: FOR l_lines_accts_rec IN
2203: (
2204: SELECT accts.*
2205: FROM psb_ws_lines lines ,
2206: psb_ws_account_lines accts
2207: WHERE lines.worksheet_id = p_source_worksheet_id
2208: AND lines.account_line_id = accts.account_line_id
2209: AND accts.position_line_id IS NULL
2286: IF ( l_ws_account_lines_csr%NOTFOUND ) THEN
2287: --
2288: -- It means a new line was created in the p_source_worksheet_id.
2289: -- Putting this line in the p_target_worksheet_id. The PSB_WS_Acct_Pvt
2290: -- API will also maintain psb_ws_lines.
2291: --
2292: PSB_WS_Acct_Pvt.Create_Account_Dist
2293: (
2294: p_api_version => 1.0 ,
2420:
2421: FOR l_lines_pos_rec IN
2422: (
2423: SELECT positions.*
2424: FROM psb_ws_lines_positions lines ,
2425: psb_ws_position_lines positions
2426: WHERE lines.worksheet_id = p_source_worksheet_id
2427: AND lines.position_line_id = positions.position_line_id
2428:
2440: IF ( l_ws_position_lines_csr%NOTFOUND ) THEN
2441: --
2442: -- It means a new line was created in the p_source_worksheet_id.
2443: -- Putting this line in the p_target_worksheet_id. The API will
2444: -- also maintain psb_ws_lines_positions.
2445: --
2446: PSB_WS_Pos_Pvt.Create_Position_Lines
2447: (
2448: p_api_version => 1.0 ,
2505: WHERE position_line_id = l_lines_pos_rec.position_line_id
2506: AND account_line_id IN
2507: (
2508: SELECT account_line_id
2509: FROM psb_ws_lines
2510: WHERE worksheet_id = p_target_worksheet_id
2511: ) ;
2512: */
2513:
2779: WHERE position_line_id = l_lines_pos_rec.position_line_id
2780: AND account_line_id IN
2781: (
2782: SELECT account_line_id
2783: FROM psb_ws_lines
2784: WHERE worksheet_id = p_target_worksheet_id
2785: ) ;
2786: */
2787:
2787:
2788: FOR l_lines_accts_rec IN
2789: (
2790: SELECT accts.*
2791: FROM psb_ws_lines lines ,
2792: psb_ws_account_lines accts
2793: WHERE lines.worksheet_id = p_source_worksheet_id
2794: AND lines.account_line_id = accts.account_line_id
2795: AND accts.position_line_id = l_lines_pos_rec.position_line_id
2871: IF ( l_ws_account_lines_csr%NOTFOUND ) THEN
2872: --
2873: -- It means a new line was created in the p_source_worksheet_id.
2874: -- Putting this line in the p_target_worksheet_id. The PSB_WS_Acct_Pvt
2875: -- API will also maintain psb_ws_lines.
2876: --
2877:
2878: PSB_WS_Acct_Pvt.Create_Account_Dist
2879: (
3727: +===========================================================================*/
3728: --
3729: -- This API adds a given account line to a worksheet. The addition is
3730: -- propagated to all the higher worksheets. The operation is performed only
3731: -- on the psb_ws_lines table.
3732: --
3733: PROCEDURE Add_Worksheet_Line
3734: (
3735: p_api_version IN NUMBER ,
3755: --
3756: CURSOR l_ws_lines_csr
3757: IS
3758: SELECT *
3759: FROM psb_ws_lines
3760: WHERE account_line_id = p_account_line_id ;
3761: --
3762: l_ws_lines_row_type l_ws_lines_csr%ROWTYPE ;
3763: --
3940: | PROCEDURE Add_Worksheet_Line |
3941: +===========================================================================*/
3942: --
3943: -- This API propagates all account lines for a Position instance to all the
3944: -- higher worksheets. The operation is performed only on the psb_ws_lines table.
3945: --
3946: PROCEDURE Add_Worksheet_Line
3947: ( p_api_version IN NUMBER,
3948: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3966: cursor c_WL is
3967: select b.account_line_id,
3968: a.view_line_flag,
3969: a.freeze_flag
3970: from psb_ws_lines a,
3971: psb_ws_account_lines b
3972: where a.account_line_id = b.account_line_id
3973: and a.worksheet_id = p_worksheet_id
3974: and b.position_line_id = p_position_line_id;
4099: --
4100: CURSOR l_ws_lines_csr
4101: IS
4102: SELECT *
4103: FROM psb_ws_lines
4104: WHERE account_line_id = p_account_line_id ;
4105: --
4106: l_ws_lines_row_type l_ws_lines_csr%ROWTYPE ;
4107: --
4210: +===========================================================================*/
4211: --
4212: -- This API adds a given position line to a worksheet. The addition is
4213: -- propagated to all the higher worksheets. The operation is performed only
4214: -- on the psb_ws_lines_positions table.
4215: --
4216: PROCEDURE Add_Worksheet_Position_Line
4217: (
4218: p_api_version IN NUMBER ,
4238: --
4239: CURSOR l_ws_lines_positions_csr
4240: IS
4241: SELECT *
4242: FROM psb_ws_lines_positions
4243: WHERE position_line_id = p_position_line_id ;
4244: --
4245: l_ws_lines_positions_row_type
4246: l_ws_lines_positions_csr%ROWTYPE ;
4454: --
4455: CURSOR l_ws_lines_positions_csr
4456: IS
4457: SELECT *
4458: FROM psb_ws_lines_positions
4459: WHERE position_line_id = p_position_line_id ;
4460: --
4461: l_ws_lines_positions_row_type l_ws_lines_positions_csr%ROWTYPE ;
4462: --
4577: p_msg_count OUT NOCOPY NUMBER ,
4578: p_msg_data OUT NOCOPY VARCHAR2 ,
4579: --
4580: p_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
4581: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE
4582: )
4583: IS
4584: --
4585: l_api_name CONSTANT VARCHAR2(30) := 'Freeze_Worksheet' ;
4725: END IF;
4726: --
4727:
4728:
4729: -- Update freeze_flag in psb_ws_lines.
4730: UPDATE psb_ws_lines
4731: SET freeze_flag = p_freeze_flag
4732: WHERE worksheet_id = p_worksheet_id;
4733:
4726: --
4727:
4728:
4729: -- Update freeze_flag in psb_ws_lines.
4730: UPDATE psb_ws_lines
4731: SET freeze_flag = p_freeze_flag
4732: WHERE worksheet_id = p_worksheet_id;
4733:
4734:
4739:
4740:
4741: IF l_budget_by_position = 'Y' THEN
4742: --
4743: -- Update freeze_flag in psb_ws_lines_positions.
4744: --
4745: UPDATE psb_ws_lines_positions
4746: SET freeze_flag = p_freeze_flag
4747: WHERE worksheet_id = p_worksheet_id;
4741: IF l_budget_by_position = 'Y' THEN
4742: --
4743: -- Update freeze_flag in psb_ws_lines_positions.
4744: --
4745: UPDATE psb_ws_lines_positions
4746: SET freeze_flag = p_freeze_flag
4747: WHERE worksheet_id = p_worksheet_id;
4748: --
4749: END IF;
4958: WHERE l_target_stage_seq > current_stage_seq
4959: AND end_stage_seq is null
4960: AND account_line_id IN
4961: ( SELECT account_line_id
4962: FROM psb_ws_lines
4963: WHERE worksheet_id = p_worksheet_id ) ;
4964:
4965: IF l_budget_by_position = 'Y' THEN
4966: --
4972: WHERE l_target_stage_seq > current_stage_seq
4973: AND end_stage_seq is null
4974: AND position_line_id IN
4975: ( SELECT position_line_id
4976: FROM psb_ws_lines_positions
4977: WHERE worksheet_id = p_worksheet_id ) ;
4978:
4979: --
4980: -- Update all the lines in psb_ws_element_lines as no service package
4985: WHERE l_target_stage_seq > current_stage_seq
4986: AND end_stage_seq is null
4987: AND position_line_id IN
4988: ( SELECT position_line_id
4989: FROM psb_ws_lines_positions
4990: WHERE worksheet_id = p_worksheet_id ) ;
4991: --
4992: END IF;
4993:
5001: WHERE l_target_stage_seq > current_stage_seq
5002: AND end_stage_seq is null
5003: AND account_line_id IN
5004: ( SELECT account_line_id
5005: FROM psb_ws_lines
5006: WHERE worksheet_id = p_worksheet_id
5007: )
5008: AND service_package_id IN
5009: ( SELECT service_package_id
5022: WHERE l_target_stage_seq > current_stage_seq
5023: AND end_stage_seq is null
5024: AND position_line_id IN
5025: ( SELECT position_line_id
5026: FROM psb_ws_lines_positions
5027: WHERE worksheet_id = p_worksheet_id
5028: )
5029: AND service_package_id IN
5030: ( SELECT service_package_id
5041: WHERE l_target_stage_seq > current_stage_seq
5042: AND end_stage_seq is null
5043: AND position_line_id IN
5044: ( SELECT position_line_id
5045: FROM psb_ws_lines_positions
5046: WHERE worksheet_id = p_worksheet_id
5047: )
5048: AND service_package_id IN
5049: ( SELECT service_package_id
5678: l_target_local_copy_flag psb_worksheets.local_copy_flag%TYPE ;
5679: l_target_global_worksheet_id psb_worksheets.worksheet_id%TYPE ;
5680: --
5681: l_budget_calendar_id psb_worksheets.budget_calendar_id%TYPE ;
5682: l_ws_lines_rec psb_ws_lines%ROWTYPE ;
5683: l_ws_lines_positions_rec psb_ws_lines_positions%ROWTYPE ;
5684: /* Bug No 2378285 Start */
5685: l_gl_cutoff_period DATE;
5686: /* Bug No 2378285 End */
5679: l_target_global_worksheet_id psb_worksheets.worksheet_id%TYPE ;
5680: --
5681: l_budget_calendar_id psb_worksheets.budget_calendar_id%TYPE ;
5682: l_ws_lines_rec psb_ws_lines%ROWTYPE ;
5683: l_ws_lines_positions_rec psb_ws_lines_positions%ROWTYPE ;
5684: /* Bug No 2378285 Start */
5685: l_gl_cutoff_period DATE;
5686: /* Bug No 2378285 End */
5687:
5826: --
5827: FOR l_account_line_id_rec IN
5828: (
5829: SELECT lines.account_line_id
5830: FROM psb_ws_lines lines ,
5831: psb_ws_account_lines accts
5832: WHERE lines.worksheet_id = p_source_worksheet_id
5833: AND lines.account_line_id = accts.account_line_id
5834: /*For Bug No : 2236283 Start*/
5859: )
5860: /*For Bug No : 2236283 End*/
5861: MINUS
5862: SELECT lines.account_line_id
5863: FROM psb_ws_lines lines
5864: WHERE worksheet_id = p_target_worksheet_id
5865: )
5866: LOOP
5867:
5865: )
5866: LOOP
5867:
5868: SELECT * INTO l_ws_lines_rec
5869: FROM psb_ws_lines
5870: WHERE worksheet_id = p_source_worksheet_id
5871: AND account_line_id = l_account_line_id_rec.account_line_id ;
5872:
5873: --
5905: --
5906: FOR l_lines_pos_rec IN
5907: (
5908: SELECT lines.position_line_id
5909: FROM psb_ws_lines_positions lines ,
5910: psb_ws_position_lines pos
5911: WHERE lines.worksheet_id = p_source_worksheet_id
5912: AND lines.position_line_id = pos.position_line_id
5913: /*For Bug No : 2236283 Start*/
5951: )
5952: /*For Bug No : 2236283 End*/
5953: MINUS
5954: SELECT position_line_id
5955: FROM psb_ws_lines_positions
5956: WHERE worksheet_id = p_target_worksheet_id
5957: )
5958: LOOP
5959:
5957: )
5958: LOOP
5959:
5960: SELECT * INTO l_ws_lines_positions_rec
5961: FROM psb_ws_lines_positions
5962: WHERE worksheet_id = p_source_worksheet_id
5963: AND position_line_id = l_lines_pos_rec.position_line_id ;
5964:
5965: --
6041: -- short as possible for better documentaion.
6042: --
6043: PROCEDURE Create_Local_Dist_Pvt
6044: (
6045: p_account_line_id IN psb_ws_lines.account_line_id%TYPE ,
6046: p_new_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
6047: p_new_position_line_id IN psb_ws_lines_positions.position_line_id%TYPE ,
6048: p_return_status OUT NOCOPY VARCHAR2
6049: )
6043: PROCEDURE Create_Local_Dist_Pvt
6044: (
6045: p_account_line_id IN psb_ws_lines.account_line_id%TYPE ,
6046: p_new_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
6047: p_new_position_line_id IN psb_ws_lines_positions.position_line_id%TYPE ,
6048: p_return_status OUT NOCOPY VARCHAR2
6049: )
6050: IS
6051: --
6135: l_period_amount_tbl(59) := l_accts_rec.period59_amount ;
6136: l_period_amount_tbl(60) := l_accts_rec.period60_amount ;
6137:
6138: --
6139: -- Create records in psb_ws_lines and psb_ws_account_lines for the
6140: -- new worksheet.
6141: --
6142: PSB_WS_Acct_Pvt.Create_Account_Dist
6143: (
6206: /*===========================================================================+
6207: | PROCEDURE Insert_WS_Lines_Pvt ( Private ) |
6208: +===========================================================================*/
6209: --
6210: -- The private procedure inserts a new record in psb_ws_lines table.
6211: --
6212: PROCEDURE Insert_WS_Lines_Pvt
6213: (
6214: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
6210: -- The private procedure inserts a new record in psb_ws_lines table.
6211: --
6212: PROCEDURE Insert_WS_Lines_Pvt
6213: (
6214: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
6215: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6211: --
6212: PROCEDURE Insert_WS_Lines_Pvt
6213: (
6214: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
6215: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6212: PROCEDURE Insert_WS_Lines_Pvt
6213: (
6214: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
6215: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6213: (
6214: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
6215: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6221: p_created_by IN psb_ws_lines.created_by%TYPE,
6214: p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
6215: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6221: p_created_by IN psb_ws_lines.created_by%TYPE,
6222: p_creation_date IN psb_ws_lines.creation_date%TYPE,
6215: p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6221: p_created_by IN psb_ws_lines.created_by%TYPE,
6222: p_creation_date IN psb_ws_lines.creation_date%TYPE,
6223: p_return_status OUT NOCOPY VARCHAR2
6216: p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6221: p_created_by IN psb_ws_lines.created_by%TYPE,
6222: p_creation_date IN psb_ws_lines.creation_date%TYPE,
6223: p_return_status OUT NOCOPY VARCHAR2
6224: )
6217: p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6221: p_created_by IN psb_ws_lines.created_by%TYPE,
6222: p_creation_date IN psb_ws_lines.creation_date%TYPE,
6223: p_return_status OUT NOCOPY VARCHAR2
6224: )
6225: IS
6218: p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
6219: p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
6220: p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
6221: p_created_by IN psb_ws_lines.created_by%TYPE,
6222: p_creation_date IN psb_ws_lines.creation_date%TYPE,
6223: p_return_status OUT NOCOPY VARCHAR2
6224: )
6225: IS
6226: --
6229: BEGIN
6230: --
6231: p_return_status := FND_API.G_RET_STS_SUCCESS ;
6232: --
6233: update psb_ws_lines
6234: set freeze_flag = p_freeze_flag,
6235: view_line_flag = p_view_line_flag,
6236: last_update_date = g_current_date,
6237: last_updated_by = g_current_user_id,
6240: and worksheet_id = p_worksheet_id;
6241:
6242: IF SQL%NOTFOUND THEN
6243:
6244: INSERT INTO psb_ws_lines
6245: (
6246: worksheet_id,
6247: account_line_id,
6248: freeze_flag,
6286: | PROCEDURE Delete_Worksheet_Pvt ( Private ) |
6287: +===========================================================================*/
6288: --
6289: -- This API deletes an official worksheet by performing deletes on
6290: -- psb_worksheets and matrix tables (psb_ws_lines and psb_ws_lines_positions).
6291: -- It also deletes worksheet related data from other tables.
6292: --
6293: PROCEDURE Delete_Worksheet_Pvt
6294: (
6300: IS
6301: --
6302: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet_Pvt' ;
6303: --
6304: l_account_line_id psb_ws_lines.account_line_id%TYPE;
6305: l_position_line_id psb_ws_lines_positions.position_line_id%TYPE ;
6306: l_local_copy_flag psb_worksheets.local_copy_flag%TYPE;
6307: l_budget_by_position psb_worksheets.budget_by_position%TYPE ;
6308: l_return_status VARCHAR2(1) ;
6301: --
6302: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet_Pvt' ;
6303: --
6304: l_account_line_id psb_ws_lines.account_line_id%TYPE;
6305: l_position_line_id psb_ws_lines_positions.position_line_id%TYPE ;
6306: l_local_copy_flag psb_worksheets.local_copy_flag%TYPE;
6307: l_budget_by_position psb_worksheets.budget_by_position%TYPE ;
6308: l_return_status VARCHAR2(1) ;
6309: /*For Bug No : 2266309 Start*/
6313: --
6314: CURSOR l_ws_account_lines_csr
6315: IS
6316: SELECT account_line_id
6317: FROM psb_ws_lines
6318: WHERE worksheet_id = p_worksheet_id;
6319:
6320: CURSOR l_ws_position_lines_csr
6321: IS
6319:
6320: CURSOR l_ws_position_lines_csr
6321: IS
6322: SELECT position_line_id
6323: FROM psb_ws_lines_positions
6324: WHERE worksheet_id = p_worksheet_id;
6325:
6326: BEGIN
6327: /*For Bug No : 2266309 Start*/
6418: END IF;
6419: /*For Bug No : 2266309 End*/
6420:
6421: --
6422: -- Delete worksheet related account lines from psb_ws_lines ( Account Matrix).
6423: --
6424: DELETE psb_ws_lines
6425: WHERE worksheet_id = p_worksheet_id ;
6426:
6420:
6421: --
6422: -- Delete worksheet related account lines from psb_ws_lines ( Account Matrix).
6423: --
6424: DELETE psb_ws_lines
6425: WHERE worksheet_id = p_worksheet_id ;
6426:
6427: /*For Bug No : 2266309 Start*/
6428: COMMIT WORK;
6429: SAVEPOINT Delete_Worksheet_Pvt;
6430: /*For Bug No : 2266309 End*/
6431:
6432: --
6433: -- Delete worksheet related position lines from psb_ws_lines_positions
6434: -- ( Position Matrix).
6435: --
6436: IF p_budget_by_position = 'Y' THEN
6437: --
6434: -- ( Position Matrix).
6435: --
6436: IF p_budget_by_position = 'Y' THEN
6437: --
6438: DELETE psb_ws_lines_positions
6439: WHERE worksheet_id = p_worksheet_id ;
6440: --
6441: /*For Bug No : 2266309 Start*/
6442: COMMIT WORK;
6818: WHERE worksheet_id = p_worksheet_id
6819: AND operation_id = NVL( p_service_package_operation_id ,
6820: FND_API.G_MISS_NUM ) ;
6821: --
6822: -- Maintain psb_ws_lines_positions table if worksheet contains positions.
6823: --
6824: IF l_budget_by_position = 'Y' THEN
6825:
6826: --
6830: FOR l_lines_pos_rec IN
6831: (
6832: SELECT pos_lines.* ,
6833: pos.position_id
6834: FROM psb_ws_lines_positions pos_lines ,
6835: psb_ws_position_lines pos ,
6836: psb_positions positions
6837: WHERE pos_lines.worksheet_id = p_worksheet_id
6838: AND pos.position_line_id = pos_lines.position_line_id
6862: -- At least one line should get created for the worksheet.
6863: l_lines_added := l_lines_added + 1 ;
6864:
6865: --
6866: -- Put the position_line in the psb_ws_lines_position table.
6867: --
6868: PSB_WS_Pos_Pvt.Create_Position_Matrix
6869: (
6870: p_api_version => 1.0 ,
6887: END IF;
6888: --
6889:
6890: --
6891: -- Maintain psb_ws_lines matrix for the account lines related to
6892: -- current position_line_id (l_lines_pos_rec.position_line_id).
6893: --
6894: FOR l_lines_rec IN
6895: (
6893: --
6894: FOR l_lines_rec IN
6895: (
6896: SELECT lines.*
6897: FROM psb_ws_lines lines,
6898: psb_ws_account_lines accts
6899: WHERE accts.position_line_id = l_lines_pos_rec.position_line_id
6900: AND lines.worksheet_id = p_worksheet_id
6901: AND lines.account_line_id = accts.account_line_id