[Home] [Help]
1: PACKAGE BODY PSB_GL_Interface_PVT AS
2: /* $Header: PSBVOGLB.pls 120.25 2007/10/19 08:52:36 rkotha ship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_GL_Interface_PVT';
5:
1: PACKAGE BODY PSB_GL_Interface_PVT AS
2: /* $Header: PSBVOGLB.pls 120.25 2007/10/19 08:52:36 rkotha ship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_GL_Interface_PVT';
5:
6: g_posting_completed BOOLEAN := FALSE;
7: g_budget_by_position VARCHAR2(1);
8: g_set_of_books_id NUMBER(15);
371: sum(a.entered_cr) cr_amt,
372: a.accounting_date,
373: a.budget_version_flag,
374: sum(a.amount) amount
375: FROM psb_gl_interfaces a,
376: gl_code_combinations b
377: WHERE worksheet_id = p_worksheet_id
378: AND period_name = p_period_name
379: AND budget_source_type = g_budget_source_type
428: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
429: RAISE FND_API.G_EXC_ERROR;
430: END IF;
431:
432: INSERT INTO psb_gl_interfaces
433: (worksheet_id,
434: group_id,
435: status,
436: set_of_books_id,
1144: reference5,
1145: 8401,
1146: l_session_id,
1147: l_serial_id
1148: FROM psb_gl_interfaces
1149: WHERE worksheet_id = p_worksheet_id
1150: AND period_name = p_period_name
1151: AND budget_version_flag = 'P'
1152: AND NVL(budget_source_type, 'BP') = g_budget_source_type;
1190: BEGIN
1191:
1192: SAVEPOINT Insert_Lines_Into_GL_I;
1193:
1194: INSERT INTO gl_interface
1195: (group_id,
1196: status,
1197: ledger_id, -- Bug#4310411
1198: user_je_source_name,
1230: reference1,
1231: reference2,
1232: reference4,
1233: reference5
1234: FROM psb_gl_interfaces
1235: WHERE worksheet_id = p_worksheet_id
1236: AND NVL(budget_source_type,'BP') = g_budget_source_type;
1237:
1238: x_return_status := FND_API.G_RET_STS_SUCCESS;
1286:
1287: CURSOR l_ws_period_csr
1288: IS
1289: SELECT DISTINCT period_name
1290: FROM psb_gl_interfaces
1291: WHERE worksheet_id = p_source_id
1292: AND NVL(budget_source_type,'BP') = g_budget_source_type;
1293:
1294:
1312: -- Bug 3029168 Start. code to find period names
1313: SELECT MAX(period_num),MIN(PERIOD_NUM)
1314: INTO l_max_period,
1315: l_min_period
1316: FROM psb_gl_interfaces
1317: WHERE budget_source_type = p_event_type
1318: AND budget_year_id = g_budget_year_id
1319: AND worksheet_id = p_source_id;
1320:
1320:
1321:
1322: SELECT period_name
1323: INTO l_max_period_name
1324: FROM psb_gl_interfaces
1325: WHERE period_num = l_max_period
1326: AND budget_source_type = p_event_type
1327: AND budget_year_id = g_budget_year_id
1328: AND worksheet_id = p_source_id
1329: AND rownum = 1;
1330:
1331: SELECT period_name
1332: INTO l_min_period_name
1333: FROM psb_gl_interfaces
1334: WHERE period_num = l_min_period
1335: AND budget_source_type = p_event_type
1336: AND budget_year_id = g_budget_year_id
1337: AND worksheet_id = p_source_id
1473:
1474: END IF;
1475:
1476: ELSE
1477: -- FOR GL_INTERFACE route.
1478: Insert_Lines_Into_GL_I
1479: (x_return_status => l_return_status,
1480: p_worksheet_id => p_source_id
1481: );
1484: RAISE FND_API.G_EXC_ERROR;
1485: END IF;
1486:
1487: -- Now spawn the "GLLEZL" CP to create the journel to GL.
1488: SELECT gl_interface_control_s.NEXTVAL,
1489: gl_journal_import_s.NEXTVAL
1490: INTO l_group_id,
1491: l_interface_run_id
1492: FROM dual;
1490: INTO l_group_id,
1491: l_interface_run_id
1492: FROM dual;
1493:
1494: INSERT INTO gl_interface_control
1495: (JE_SOURCE_NAME,
1496: STATUS,
1497: INTERFACE_RUN_ID,
1498: GROUP_ID,
1742:
1743: /*---------------------------------------------------------------------*/
1744:
1745: -- Wrapper routine FOR FND_FLEX_APIS. The reason FOR this IS procedure
1746: -- IS to be called FROM PSBSTGLS.fmb (Setup GL Interfaces) form.
1747: -- Call the fnd_flex_apis directly causes a GPF AND I suspect it IS
1748: -- due to the 64 K size limit
1749:
1750: PROCEDURE Get_Qualifier_Segnum
2211:
2212: -- ++ FOR each detail_rec, process 2 times - one FOR Permanent AND another
2213: -- ++ FOR 'ALL' (IF all exists');
2214: FOR i IN 1 .. 2 LOOP
2215: -- ++ post to gl_interface FOR both permanent AND all
2216: IF i = 1 THEN
2217: l_budget_version_flag := 'P';
2218: ELSE
2219: l_budget_version_flag := 'A';
2253: CLOSE c_bgversion;
2254: END IF;
2255:
2256: --++
2257: --++ THEN INTO psb_gl_interfaces
2258: --++
2259: INSERT INTO psb_gl_interfaces
2260: (worksheet_id,
2261: group_id,
2255:
2256: --++
2257: --++ THEN INTO psb_gl_interfaces
2258: --++
2259: INSERT INTO psb_gl_interfaces
2260: (worksheet_id,
2261: group_id,
2262: status,
2263: set_of_books_id,
2357: l_ccid := c_summary_rec.code_combination_id;
2358: END IF;
2359:
2360: FOR i IN 1 .. 2 LOOP
2361: -- ++ post to gl_interface FOR both permanent AND all FOR each record
2362: IF i = 1 THEN
2363: l_budget_version_flag := 'P';
2364: ELSE
2365: l_budget_version_flag := 'A';
2397: RAISE FND_API.G_EXC_ERROR;
2398: END IF;
2399:
2400: --++
2401: --++ THEN INTO psb_gl_interfaces
2402: --++
2403: INSERT INTO psb_gl_interfaces
2404: (worksheet_id,
2405: group_id,
2399:
2400: --++
2401: --++ THEN INTO psb_gl_interfaces
2402: --++
2403: INSERT INTO psb_gl_interfaces
2404: (worksheet_id,
2405: group_id,
2406: status,
2407: set_of_books_id,
2540: FOR bgt_stage_rec IN bgt_stage LOOP
2541: l_stage_seq := bgt_stage_rec.sequence_number;
2542: END LOOP;
2543:
2544: --++ INSERT INTO PSB_GL_INTERFACE Table.
2545: Insert_Lines_Into_PSB_I_Fund
2546: (x_return_status => l_return_status ,
2547: x_msg_count => l_msg_count,
2548: x_msg_data => l_msg_data,
2612: BEGIN
2613:
2614: SAVEPOINT Delete_Old_Run;
2615:
2616: DELETE FROM psb_gl_interfaces
2617: WHERE worksheet_id = p_worksheet_id
2618: AND NVL(budget_source_type, 'P') = p_budget_source_type;
2619:
2620: x_return_status := FND_API.G_RET_STS_SUCCESS;
3363: l_period_indx NUMBER :=0;
3364: l_tbl_indx NUMBER :=0;
3365:
3366: -- Bug#4310411 Start
3367: -- Record types those will hold the PSB_GL_INTERFACES
3368: -- table's columns.
3369: rec_budget_version_id Number_Tbl_Type;
3370: rec_accounting_date Date_Tbl_Type;
3371: rec_period_name Char_Tbl_Type;
3469: -- (post to the 'A' version flag budget AND 'P' version flag budget)
3470: -- AND once FOR temporary revision (post only to 'A' version flag)
3471:
3472: FOR i IN 1 .. 2 LOOP
3473: -- post to gl_interface FOR both permanent AND temporary revisions
3474:
3475: IF i = 1 THEN
3476: l_budget_version_flag := 'A';
3477: ELSE
3480:
3481: l_budget_version_id := c_budrev_accts_rec.gl_budget_version_id;
3482:
3483: IF i = 2 THEN
3484: -- post to gl_interface FOR only permanent revision
3485: IF g_permanent_revision = 'N' THEN
3486: EXIT;
3487: -- skip posting since this IS NOT a permanent revision
3488: END IF;
3514: END IF;
3515: END IF;
3516:
3517: --
3518: -- THEN INSERT INTO psb_gl_interfaces
3519: --
3520:
3521: l_tbl_indx := l_tbl_indx + 1;
3522:
3539: -- change occured.
3540: l_period_indx := l_period_indx + 1;
3541: l_rec_period_name(l_period_indx) := l_gl_period_name;
3542:
3543: --++ Now Bulk Insert the Data INTO PSB_GL_INTERFACES.
3544: IF rec_budget_version_id.COUNT > 0 THEN
3545: FORALL l_indx IN 1..rec_budget_version_id.COUNT
3546: INSERT INTO psb_gl_interfaces
3547: (worksheet_id,
3542:
3543: --++ Now Bulk Insert the Data INTO PSB_GL_INTERFACES.
3544: IF rec_budget_version_id.COUNT > 0 THEN
3545: FORALL l_indx IN 1..rec_budget_version_id.COUNT
3546: INSERT INTO psb_gl_interfaces
3547: (worksheet_id,
3548: group_id,
3549: status,
3550: set_of_books_id,
4757: IS
4758: -- local variables
4759: l_api_name CONSTANT VARCHAR2(30) := 'Find Document Posting Status';
4760:
4761: /* for bug 4654145 --> Check the psb_gl_interfaces table whether the BR has been posted or not */
4762: l_no NUMBER;
4763:
4764: BEGIN
4765: IF p_Document_Type = 'BR' THEN
4764: BEGIN
4765: IF p_Document_Type = 'BR' THEN
4766: SELECT 1
4767: INTO l_no
4768: FROM psb_gl_interfaces
4769: WHERE worksheet_id = p_document_id
4770: AND budget_source_type = p_document_type
4771: AND rownum = 1;
4772:
4789: END Find_Document_Posting_Status;
4790: /* END bug 3659531 */
4791:
4792:
4793: END PSB_GL_Interface_PVT;
4794: