[Home] [Help]
311: ,sum(decode(pkt.parent_bc_packet_id, NULL,(nvl(pkt.accounted_dr,0) - nvl(pkt.accounted_cr,0)),0)
312: ) pktRawAmt
313: Into l_pktbdamt
314: ,l_pktrawamt
315: from pa_bc_packets pkt
316: Where pkt.project_id = p_project_id
317: and pkt.task_id = p_task_id
318: and pkt.budget_version_id = p_budget_version_id
319: and pkt.document_line_id = p_document_line_id
584: cursor cur_potrxs IS
585: select 'Y'
586: from dual
587: where exists (select null
588: from pa_bc_packets pkt
589: where pkt.packet_id = p_packet_id
590: and pkt.document_type = 'PO'
591: );
592:
602: ,NULL) expenditure_type
603: ,decode(pt.burden_amt_display_method,'D'
604: ,decode(pkt.parent_bc_packet_id,NULL,'RAW','BURDEN')
605: ,'RAW') line_type
606: from pa_bc_packets pkt
607: ,pa_projects_all pp
608: ,pa_project_types_all pt
609: where pkt.packet_id = p_packet_id
610: and pkt.document_line_id is NOT NULL -- with R12 this check is not sufficient to find if the PO is an CWK PO
670: select 'Y'
671: from dual
672: where exists
673: (select null
674: from pa_bc_packets pkts1
675: where pkts1.document_line_id is NOT NULL
676: and pkts1.status_code in ('A','P','C','I')
677: and substr(NVL(pkts1.result_code,'P'),1,1) = 'P'
678: and nvl(pkts1.funds_process_mode,'T') <> 'B'
715: decode(pkt.document_type,'PO'
716: ,decode(pkt.parent_bc_packet_id,NULL ,0
717: ,decode(lv_bd_disp_method, 'D'
718: ,decode(pkt.expenditure_type,lv_expenditure_type,1,0),1)),0)) relevd_comm_bd_amt
719: FROM pa_bc_packets pkt
720: WHERE pkt.project_id = lv_project_id
721: AND pkt.budget_version_id = lv_budget_version_id
722: AND pkt.task_id = lv_task_id
723: AND pkt.document_line_id = lv_document_line_id
859: CLOSE cur_cwk_amts;
860: pa_funds_control_pkg.log_message(p_msg_token1 => 'CommRawAmt['||l_comm_raw_amt||']CommbdAmt['||l_comm_bd_amt||
861: ']RelvdComm['||l_relvd_comm_raw_amt||']RelvdBd['||l_relvd_comm_bd_amt||']');
862: l_rows_updated := 0;
863: UPDATE pa_bc_packets pkt
864: SET pkt.comm_tot_raw_amt = nvl(pkt.comm_tot_raw_amt,0) +
865: decode(p_calling_module,'GL',decode(cwk.line_type,'RAW',nvl(l_comm_raw_amt,0),0),0)
866: ,pkt.comm_tot_bd_amt = nvl(pkt.comm_tot_bd_amt,0) +
867: decode(p_calling_module,'GL'
914: l_rows_updated := 0;
915: IF nvl(l_commsummrec,'N') = 'N' and nvl(l_pktsummrec,'N') = 'N' and l_po_exists = 'Y' Then
916: l_stage := 'Updating packets summary record flag';
917: pa_funds_control_pkg.log_message(p_msg_token1=> l_stage);
918: UPDATE pa_bc_packets pkt
919: SET pkt.summary_record_flag = decode (pkt.summary_record_flag,NULL,'Y',pkt.summary_record_flag)
920: WHERE pkt.packet_id = p_packet_id
921: AND pkt.document_type = 'PO'
922: AND nvl(pkt.funds_process_mode,'N') = 'T'
931: pkt.parent_bc_packet_id is NOT NULL
932: and pkt.expenditure_type = cwk.expenditure_type
933: and cwk.burden_amt_display_method = 'D'
934: and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
935: from pa_bc_packets pkt1
936: where pkt1.packet_id = pkt.packet_id
937: and pkt1.project_id = pkt.project_id
938: and pkt1.task_id = pkt.task_id
939: and pkt1.budget_version_id = pkt.budget_version_id
947: ( -- sep line burden lines only one raw line should be stamped with summary record info
948: pkt.parent_bc_packet_id is NULL
949: and cwk.burden_amt_display_method = 'D'
950: and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
951: from pa_bc_packets pkt1
952: where pkt1.packet_id = pkt.packet_id
953: and pkt1.project_id = pkt.project_id
954: and pkt1.task_id = pkt.task_id
955: and pkt1.budget_version_id = pkt.budget_version_id
963: ( -- raw line should be stamped with summary record info if display method is same
964: pkt.parent_bc_packet_id is NULL
965: and cwk.burden_amt_display_method <> 'D'
966: and pkt.bc_packet_id = (select min(pkt1.bc_packet_id)
967: from pa_bc_packets pkt1
968: where pkt1.packet_id = pkt.packet_id
969: and pkt1.project_id = pkt.project_id
970: and pkt1.task_id = pkt.task_id
971: and pkt1.budget_version_id = pkt.budget_version_id
1030: If l_cwk_multiplier is NOT NULL Then
1031: l_cwk_multiplier := pa_currency.round_trans_currency_amt
1032: (l_cwk_multiplier,g_acct_currency_code);
1033: End If;
1034: UPDATE pa_bc_packets cmt
1035: SET cmt.compiled_multiplier = decode (cmt.document_line_id,NULL,cmt.compiled_multiplier
1036: ,l_cwk_multiplier)
1037: WHERE cmt.summary_record_flag = 'Y'
1038: AND cmt.document_line_id is NOT NULL
1089: -- Obsolete FUNCTION check_encum_type
1090:
1091: ----------------------------------------------------------------------------------------
1092: --This api copies the unreserved transaction into to the packet.
1093: -- when the calling mode is unreserved then copy all the transactions from pa_bc_packets
1094: -- for the old packet id(which is funds cheked and approved) to new packet by swapping the amount
1095: -- columns and all other columns values remain same. Approve the packets with status Approved
1096: -- donot create encumbrance liquidation as GL funds checker will create reversing lines
1097: -- for the old packet id and donot populate burden rows / donot check for the unreserved packet
1157: OPEN cur_packet ;
1158: FETCH cur_packet INTO l_packet_id;
1159: CLOSE cur_packet;
1160:
1161: pa_funds_control_pkg.log_message(p_msg_token1 => 'Inserting records in pa_bc_packets');
1162:
1163: INSERT INTO pa_bc_packets
1164: ( ---- who columns------
1165: request_id,
1159: CLOSE cur_packet;
1160:
1161: pa_funds_control_pkg.log_message(p_msg_token1 => 'Inserting records in pa_bc_packets');
1162:
1163: INSERT INTO pa_bc_packets
1164: ( ---- who columns------
1165: request_id,
1166: program_id,
1167: program_application_id,
1233: l_update_login,
1234: sysdate,
1235: l_update_login,
1236: l_packet_id,
1237: pa_bc_packets_s.nextval,
1238: pbc.budget_version_id,
1239: pbc.project_id,
1240: pbc.task_id,
1241: pbc.expenditure_type,
1283: pbc.source_event_id,
1284: pbc.document_distribution_type,
1285: pbc.document_header_id_2
1286: FROM
1287: pa_bc_packets pbc
1288: WHERE pbc.packet_id = x_packet_id;
1289:
1290: If sql%rowcount > 0 then
1291: --assign the new packet id to out parameter
1297: and p_reference1 is not null and p_reference2 is not null then -- unreserved
1298: OPEN cur_packet ;
1299: FETCH cur_packet INTO l_packet_id;
1300: CLOSE cur_packet;
1301: pa_funds_control_pkg.log_message(p_msg_token1 => 'Inserting records in pa_bc_packets');
1302:
1303: INSERT INTO pa_bc_packets
1304: ( ---- who columns------
1305: request_id,
1299: FETCH cur_packet INTO l_packet_id;
1300: CLOSE cur_packet;
1301: pa_funds_control_pkg.log_message(p_msg_token1 => 'Inserting records in pa_bc_packets');
1302:
1303: INSERT INTO pa_bc_packets
1304: ( ---- who columns------
1305: request_id,
1306: program_id,
1307: program_application_id,
1373: l_update_login,
1374: sysdate,
1375: l_update_login,
1376: l_packet_id,
1377: pa_bc_packets_s.nextval,
1378: pbc.budget_version_id,
1379: pbc.project_id,
1380: pbc.task_id,
1381: pbc.expenditure_type,
1423: pbc.source_event_id,
1424: pbc.document_distribution_type,
1425: pbc.document_header_id_2
1426: FROM
1427: pa_bc_packets pbc
1428: WHERE pbc.packet_id = x_packet_id
1429: AND document_type in ('CC_C_CO','CC_P_CO')
1430: AND document_header_id = p_reference2;
1431:
1464: RETURN VARCHAR2 IS
1465:
1466: l_return_flag varchar2(10) := 'N';
1467: l_req_found_flag varchar2(1) := 'N';
1468: l_parent_bc_packet_id PA_BC_PACKETS.PARENT_BC_PACKET_ID%TYPE;
1469:
1470: -- This cursor picks the po details for the given requisition
1471: CURSOR get_podetails Is
1472: SELECT 'PO'
1485: AND comm.document_type = 'REQ'
1486: UNION ALL
1487: SELECT null bc_commitment_id,
1488: pbc.parent_bc_packet_id
1489: FROM pa_bc_packets pbc
1490: WHERE pbc.document_distribution_id = p_req_distribution_id
1491: AND pbc.document_header_id = p_req_header_id
1492: AND pbc.document_type = 'REQ'
1493: AND pbc.balance_posted_flag = 'N'
1592: l_po_found_flag VARCHAR2(1) := 'N';
1593: l_cc_found_flag VARCHAR2(1) := 'N';
1594: l_return_flag VARCHAR2(1) := 'N';
1595: l_cc_det_pf_line_id varchar2(30); --Bug 6393954 changed from number to varchar2
1596: L_PARENT_BC_PACKET_ID PA_BC_PACKETS.PARENT_BC_PACKET_ID%TYPE;
1597: L_CC_HEADER_ID varchar2(30); --Bug 6393954 changed from PO_DISTRIBUTIONS_ALL.PO_HEADER_ID%TYPE
1598: L_PO_DESTINATION_TYPE PO_DISTRIBUTIONS_ALL.DESTINATION_TYPE_CODE%TYPE;
1599: L_CC_DISTRIBUTION_ID NUMBER;
1600:
1625: AND comm.document_type = p_document_type
1626: AND NVL(comm.document_header_id_2 ,-99) = NVL(p_po_release_id,-99)
1627: UNION ALL
1628: SELECT pbc.parent_bc_packet_id
1629: FROM pa_bc_packets pbc
1630: WHERE pbc.document_distribution_id = p_distribution_id
1631: AND pbc.document_header_id = p_header_id
1632: AND pbc.document_type = p_document_type
1633: AND pbc.balance_posted_flag = 'N'
1825: g_tab_period_name(p_new_rec_index) := g_tab_period_name(p_copy_from_index);
1826: g_tab_parent_reversal_id(p_new_rec_index) := g_tab_parent_reversal_id(p_copy_from_index); -- Bug 5406690
1827:
1828: -- Bug 5406690
1829: select pa_bc_packets_s.nextval
1830: into g_tab_bc_packet_id(p_new_rec_index)
1831: from dual;
1832:
1833: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
2207: PRAGMA AUTONOMOUS_TRANSACTION;
2208:
2209: CURSOR c_pkt_SOB IS
2210: SELECT DISTINCT pbc.set_of_books_id
2211: FROM pa_bc_packets pbc
2212: WHERE pbc.packet_id = p_packet_id
2213: AND pbc.status_code = 'I'
2214: AND substr(nvl(pbc.result_code,'P'),1,1) not in ('R','F');
2215:
2223: pbc.budget_version_id,
2224: pm.entry_level_code,
2225: DECODE(pm.entry_level_code,'P',0,pt.top_task_id) top_task_id,
2226: pbc.resource_list_member_id
2227: FROM pa_bc_packets pbc,
2228: pa_tasks pt,
2229: pa_budget_versions bv,
2230: pa_budget_entry_methods pm
2231: WHERE pbc.packet_id = p_packet_id
2244: WHERE gl.application_id = 101
2245: AND gl.set_of_books_id = p_sob_id
2246: AND gl.period_name = p_period_name;
2247:
2248: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
2249: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
2250: l_return_status VARCHAR2(10) := 'S';
2251: l_error_message_code VARCHAR2(200) := NULL;
2252: l_gl_start_date DATE;
2245: AND gl.set_of_books_id = p_sob_id
2246: AND gl.period_name = p_period_name;
2247:
2248: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
2249: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
2250: l_return_status VARCHAR2(10) := 'S';
2251: l_error_message_code VARCHAR2(200) := NULL;
2252: l_gl_start_date DATE;
2253:
2312: pa_funds_control_pkg.log_message(p_msg_token1 => 'l_return_status = '||l_return_status);
2313: pa_funds_control_pkg.log_message(p_msg_token1 => 'l_error_message_code = '||l_error_message_code);
2314: End if;
2315:
2316: -- Fail pa bc packets if there is any error while deriving the budget ccid value
2317: IF l_return_status = 'E' OR NVL(l_budget_ccid,-999) = -999 OR NVL(l_budget_line_id,-999) = -999 THEN
2318:
2319:
2320: UPDATE pa_bc_packets
2316: -- Fail pa bc packets if there is any error while deriving the budget ccid value
2317: IF l_return_status = 'E' OR NVL(l_budget_ccid,-999) = -999 OR NVL(l_budget_line_id,-999) = -999 THEN
2318:
2319:
2320: UPDATE pa_bc_packets
2321: set budget_ccid = l_budget_ccid,
2322: budget_line_id = l_budget_line_id,
2323: status_code = DECODE(status_code,'F',status_code,'R',status_code,'T',status_code,DECODE(p_bc_mode,'C','F','R')),
2324: result_code = DECODE(substr(result_code,1,1),'F',result_code,'F132'),
2342:
2343: ELSE
2344:
2345:
2346: UPDATE pa_bc_packets
2347: set budget_ccid = l_budget_ccid,
2348: budget_line_id = l_budget_line_id
2349: WHERE packet_id = p_packet_id
2350: AND status_code ='I'
2447: PRAGMA AUTONOMOUS_TRANSACTION;
2448:
2449: CURSOR c_pkt_status IS
2450: SELECT 1
2451: FROM pa_bc_packets
2452: WHERE packet_id = p_packet_id
2453: AND (status_code in ('F','T','R')
2454: OR SUBSTR (result_code,1,1) = 'F');
2455:
2474: IF l_counter > 0 THEN
2475:
2476: x_return_code := 'F' ;
2477:
2478: UPDATE pa_bc_packets a
2479: SET a.status_code = DECODE(p_bc_mode,'C','F','R'),
2480: a.result_code = DECODE( SUBSTR (result_code,1,1),'F',result_code,'F170'),
2481: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F170'),
2482: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F170'),
2517: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
2518: pa_funds_control_pkg.log_message(p_msg_token1 => 'FAIL_NULL_EVENT_PKTS : Start ');
2519: END IF;
2520:
2521: UPDATE pa_bc_packets a
2522: SET a.status_code = DECODE(p_bc_mode,'C','F','R'),
2523: a.result_code = 'F168' ,
2524: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F168'),
2525: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F168'),
2555: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
2556: pa_funds_control_pkg.log_message(p_msg_token1 => 'FAIL_DANGLING_PKTS : Start ');
2557: END IF;
2558:
2559: UPDATE pa_bc_packets
2560: SET status_code = 'T',
2561: result_code = 'F142',
2562: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,'F142'),
2563: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,'F142'),
2582: -- Called from PSA_BC_XLA_PVT.Budgetary_control after creating events for AP/PO/REQ.
2583: -- and before It performs following tasks :
2584: -- 1. Driving table for this procedure is psa_bc_xla_events_gt .Picks all AP/PO/REQ events
2585: -- created by BCPSA FC engine.
2586: -- 2. Inserts raw records into pa_bc_packets by fetching PO/REQ data from po_bc_distributions
2587: -- and AP data from ap_invoice_distributions_all table for all events in psa_bc_xla_events_gt.
2588: -- 3. Fires populate_burden_cost procedure to Insert burden records for above raw components
2589: -- 4. Fires pa_funds_control_pkg.derive_rlmi to derive resource_list_member_id on bc packets
2590: -- 5. Fires pa_funds_control_utils.get_budegt_ccid to derive budget_ccid and budget_line_id
2677: -- Bug 5403775 : Added below columns to derive pkt reference columns for backing docs such that
2678: -- they will point to the main doc
2679: POBC.origin_sequence_num,
2680: pobc.applied_to_dist_id_2,
2681: pa_bc_packets_s.nextval bc_packet_id -- Bug 5406690
2682: FROM po_bc_distributions pobc ,
2683: po_encumbrance_gt pogt,
2684: psa_bc_xla_events_gt xlaevt
2685: WHERE pobc.ae_event_id = xlaevt.event_id
3276:
3277:
3278: CURSOR c_count_success_recs(p_packet_id NUMBER) IS
3279: SELECT count(*)
3280: FROM pa_bc_packets
3281: WHERE packet_id = p_packet_id
3282: AND (NVL(status_code,'I') NOT IN ('F','T','R')
3283: AND SUBSTR (NVL(result_code,'P'),1,1) <> 'F');
3284:
3303: l_req_vendor_id PO_HEADERS_ALL.VENDOR_ID%TYPE;
3304: l_req_org_id PO_HEADERS_ALL.ORG_ID%TYPE;
3305: l_Po_vendor_id PO_HEADERS_ALL.VENDOR_ID%TYPE;
3306: l_po_org_id PO_HEADERS_ALL.ORG_ID%TYPE;
3307: l_packet_id pa_bc_packets.packet_id%TYPE;
3308: l_prepay_exists VARCHAR2(1);
3309: l_stdinvoice_exists VARCHAR2(1);
3310:
3311: BEGIN
3355: pa_funds_control_pkg.log_message(p_msg_token1 => 'CREATE_PROJ_ENCUMBRANCE_EVENTS : p_partial_flag = '||p_partial_flag);
3356: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling init_plsql_tabs to initialize the pl/sql tabs ');
3357: End if;
3358:
3359: -- Initialize the pl/sql table which stores pa_bc_packets records
3360: init_plsql_tabs;
3361:
3362: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
3363: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling PA_FUNDS_CONTROL_UTILS.init_util_variables');
3491: CLOSE cur_ap_bc_dist ;
3492:
3493: -- Bug 5406690
3494: FOR i IN 1..g_tab_doc_header_id.count LOOP
3495: select pa_bc_packets_s.nextval
3496: into g_tab_bc_packet_id(i)
3497: from dual;
3498: END LOOP;
3499:
3954: g_tab_org_id(l_index) := l_po_org_id ;
3955:
3956: -- End of Code to populate vendor_id and org_id
3957:
3958: END IF; --IF g_pa_bc_packets_tab(l_index).document_type ='REQ' THEN
3959:
3960: -- Bug 5403775 : Below logic derives reference columns on backing documents such that
3961: -- they will point to main document
3962: -- Eg :
4065: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
4066: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling update_cwk_pkt_lines ');
4067: End if;
4068:
4069: -- To update CWK related columns of pa_bc_packets PO records
4070: update_cwk_pkt_lines (p_calling_module => 'GL',
4071: p_packet_id => l_packet_id);
4072:
4073: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
4202:
4203: END CREATE_PROJ_ENCUMBRANCE_EVENTS;
4204:
4205: ----------------------------------------------------------------------------------
4206: --This is an Autonmous api which inserts records into the pa bc packets from
4207: -- plsql tables and commits
4208: ---------------------------------------------------------------------------------
4209: PROCEDURE Load_pkts (p_packet_id IN NUMBER,
4210: p_bc_mode IN VARCHAR2,
4239: l_update_login := -1;
4240: End if;
4241:
4242: FORALL i in 1 .. g_tab_set_of_books_id.count
4243: INSERT INTO PA_BC_PACKETS (
4244: request_id,
4245: program_id,
4246: program_application_id,
4247: program_update_date,
4360: );
4361:
4362:
4363: /* Added for Bug fix: 3086398 */
4364: Update pa_bc_packets
4365: set status_code = DECODE(status_code,'F',status_code,'R',status_code,'T',status_code,DECODE(p_bc_mode,'C','F','R')),
4366: res_result_code = DECODE(substr(res_result_code,1,1),'F',res_result_code,result_code),
4367: res_grp_result_code = DECODE(substr(res_grp_result_code,1,1),'F',res_grp_result_code,result_code),
4368: task_result_code = DECODE(substr(task_result_code,1,1),'F',task_result_code,result_code),
4382: ---- the same while creating liquidation entries in IGC interface table.
4383:
4384: IF p_calling_module = 'CBC' THEN
4385: FORALL I in 1..g_tab_rowid.count
4386: Update pa_bc_packets set gl_row_number = g_tab_rowid(i)
4387: where bc_packet_id = g_tab_bc_packet_id (i); END IF;
4388:
4389: -------->6599207 ------END
4390:
4461: CURSOR is_cwk_po_unreserve is
4462: select distinct pbc.document_header_id,
4463: pbc.document_line_id,
4464: org_id
4465: from pa_bc_packets pbc
4466: where packet_id = p_packet_id
4467: and document_type = 'PO'
4468: and (nvl(accounted_dr,0) - nvl(accounted_cr,0)) < 0
4469: and not exists ( select 1
4466: where packet_id = p_packet_id
4467: and document_type = 'PO'
4468: and (nvl(accounted_dr,0) - nvl(accounted_cr,0)) < 0
4469: and not exists ( select 1
4470: from pa_bc_packets
4471: where packet_id = p_packet_id
4472: and document_type <> 'PO') ;
4473:
4474: PROCEDURE Rate_PO_Unreserve( p_packet_id number,
4477: p_accounted_cr_tab pa_plsql_datatypes.NumTabTyp) is
4478: PRAGMA AUTONOMOUS_TRANSACTION;
4479: begin
4480: FORALL i IN p_bc_packet_id_tab.first .. p_bc_packet_id_tab.last
4481: update pa_bc_packets
4482: set entered_cr = p_entered_cr_tab(i),
4483: accounted_cr = p_accounted_cr_tab(i),
4484: entered_dr = 0,
4485: accounted_dr = 0
4503: ** as actuals. In this case po receipt is not created and po cancellation
4504: ** would liquidate without considering the cwk timecards. This is resulting
4505: ** into -ve balance.
4506: ** Resolution :
4507: ** We are finding out the summary amounts from pa bc packets or bc commitments
4508: ** for a po line. Summary record has total raw amount and total amout relieved
4509: ** we are checking if the po credit is creted in bc packets and comparing the
4510: ** credit amount with the po balance at line level in the summary record.
4511: ** BC packet record is updated with the amount ( whichever is less credit amount or
4543: l_amt_balance_tab,
4544: g_bdamt_balance_tab,
4545: g_doc_line_id_tab,
4546: g_burden_type_tab
4547: from pa_bc_packets pbc ,
4548: pa_projects_all pp,
4549: pa_project_types ppt
4550: where pbc.document_type = 'PO'
4551: and pbc.summary_record_flag = 'Y'
4555: and pbc.parent_bc_packet_id is NULL
4556: and pbc.project_id = pp.project_id
4557: and pp.project_type = ppt.project_type
4558: and (pbc.project_id, task_id) in ( select distinct project_id, task_id
4559: from pa_bc_packets
4560: where packet_id = p_packet_id
4561: and document_header_id = c_rate_po.document_header_id
4562: and document_line_id = c_rate_po.document_line_id )
4563: and pbc.packet_id < p_packet_id ;
4588: and pbc.document_header_id = c_rate_po.document_header_id
4589: and pbc.project_id = pp.project_id
4590: and pp.project_type = ppt.project_type
4591: and (pbc.project_id, pbc.task_id) in ( select distinct project_id, task_id
4592: from pa_bc_packets
4593: where packet_id = p_packet_id
4594: and document_header_id = c_rate_po.document_header_id
4595: and document_line_id = c_rate_po.document_line_id )
4596: and pbc.packet_id < p_packet_id ;
4619: l_entered_dr_tab,
4620: l_entered_cr_tab,
4621: l_accounted_dr_tab,
4622: l_accounted_cr_tab
4623: from pa_bc_packets
4624: where packet_id = p_packet_id
4625: and document_line_id = g_doc_line_id_tab(line_indx)
4626: and document_header_id = c_rate_po.document_header_id
4627: and project_id = g_project_id_tab(line_indx)
4734: If p_calling_module in ('DISTBTC','TRXNIMPORT','DISTVIADJ','DISTERADJ','TRXIMPORT','DISTCWKST')
4735: AND p_mode not in ('A','U') then
4736:
4737: /* PA.M changes for contingent worker functionality */
4738: /* This check is not required as this is done even before inserting the record into pa_bc_packets
4739: * during distribute process. Having this check is redudant
4740: If p_calling_module = 'DISTCWKST' then
4741: pa_funds_control_pkg.log_message(p_msg_token1 => 'Calling checkCWKbdExp Api to check burden cost codes');
4742: -- check for the burden cost codes changed if so error out the transactions
4746: );
4747: pa_funds_control_pkg.log_message(p_msg_token1 => 'End of checkCWKbdExp Api');
4748: End If;
4749: ***/
4750: /* This Query insert records into pa_bc_packets
4751: * for the projects which is of burden on same
4752: * expenditure item
4753: */
4754:
4751: * for the projects which is of burden on same
4752: * expenditure item
4753: */
4754:
4755: INSERT INTO pa_bc_packets
4756: ( ---- who columns------
4757: request_id,
4758: program_id,
4759: program_application_id,
4816: sysdate,
4817: l_update_login,
4818: ------ main columns-----------
4819: pbc.packet_id,
4820: pa_bc_packets_s.nextval,
4821: pbc.budget_version_id,
4822: pbc.project_id,
4823: pbc.task_id,
4824: pbc.expenditure_type,
4912: ,pbc.reference1
4913: ,pbc.reference2
4914: ,pbc.reference3
4915: ,pbc.exp_item_id
4916: FROM pa_bc_packets pbc
4917: WHERE pbc.packet_id = p_packet_id
4918: AND pbc.parent_bc_packet_id = -1
4919: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
4920: AND pa_funds_control_pkg.check_bdn_on_sep_item
4929:
4930: pa_funds_control_pkg.log_message(p_msg_token1 =>
4931: 'Num of records inserted ='||sql%rowcount);
4932:
4933: /* This Query insert records into pa_bc_packets
4934: * for the projects which is of burden on different
4935: * expenditure item
4936: */
4937:
4934: * for the projects which is of burden on different
4935: * expenditure item
4936: */
4937:
4938: INSERT INTO pa_bc_packets
4939: ( ---- who columns------
4940: request_id,
4941: program_id,
4942: program_application_id,
4999: sysdate,
5000: l_update_login,
5001: ------ main columns-----------
5002: pbc.packet_id,
5003: pa_bc_packets_s.nextval,
5004: pbc.budget_version_id,
5005: pbc.project_id,
5006: pbc.task_id,
5007: et.expenditure_type,
5067: pa_cost_base_exp_types cbet,
5068: pa_ind_rate_schedules_all_bg irs,
5069: pa_ind_compiled_sets ics,
5070: pa_compiled_multipliers cm,
5071: pa_bc_packets pbc
5072: WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
5073: AND cb.cost_base = cbet.cost_base
5074: AND cb.cost_base_type = cbet.cost_base_type
5075: AND et.expenditure_type = icc.expenditure_type
5120: --
5121: -- Bug : 3703180
5122: -- PJ.M:B5:P1:QA:CWK: PAXBLRSL- -VE COMMITMENT CREATED WHEN PO CANCELLED AFTER
5123: -- Resolution : Compare the burden cost calculated with the summary record in
5124: -- pa_bc_packets /pa_bc_commitments_all. We are using the amounts in the summary
5125: -- table if summary has amounts less than the calculated in pa bc packets.
5126: -- If we have zero burden in pa bc packets then we use the entire amounts in
5127: -- the summary record.
5128: --
5121: -- Bug : 3703180
5122: -- PJ.M:B5:P1:QA:CWK: PAXBLRSL- -VE COMMITMENT CREATED WHEN PO CANCELLED AFTER
5123: -- Resolution : Compare the burden cost calculated with the summary record in
5124: -- pa_bc_packets /pa_bc_commitments_all. We are using the amounts in the summary
5125: -- table if summary has amounts less than the calculated in pa bc packets.
5126: -- If we have zero burden in pa bc packets then we use the entire amounts in
5127: -- the summary record.
5128: --
5129: PROCEDURE update_cwk_po_burden(p_packet_id NUMBER ) is
5122: -- PJ.M:B5:P1:QA:CWK: PAXBLRSL- -VE COMMITMENT CREATED WHEN PO CANCELLED AFTER
5123: -- Resolution : Compare the burden cost calculated with the summary record in
5124: -- pa_bc_packets /pa_bc_commitments_all. We are using the amounts in the summary
5125: -- table if summary has amounts less than the calculated in pa bc packets.
5126: -- If we have zero burden in pa bc packets then we use the entire amounts in
5127: -- the summary record.
5128: --
5129: PROCEDURE update_cwk_po_burden(p_packet_id NUMBER ) is
5130: PRAGMA AUTONOMOUS_TRANSACTION;
5173: l_entered_dr_tab,
5174: l_entered_cr_tab,
5175: l_accounted_dr_tab,
5176: l_accounted_cr_tab
5177: from pa_bc_packets
5178: where packet_id = p_packet_id
5179: and document_line_id = g_doc_line_id_tab(line_indx)
5180: and project_id = g_project_id_tab(line_indx)
5181: and task_id = g_task_id_tab(line_indx)
5185: IF l_bc_packet_id_tab.count > 0 THEN
5186: --
5187: -- bug 3703180
5188: -- compare the burden cost with the summary record and use the summary burden cost
5189: -- if calculated burden is ZERO or less then the pa bc packets burden.
5190: --
5191:
5192: for pkt_rec in 1..l_bc_packet_id_tab.count loop
5193:
5219: end loop ;
5220:
5221: --
5222: -- BUG 3703180
5223: -- Update the calculated burden cost to pa bc packets.
5224: --
5225: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
5226: update pa_bc_packets
5227: set entered_cr = l_entered_cr_tab(i),
5222: -- BUG 3703180
5223: -- Update the calculated burden cost to pa bc packets.
5224: --
5225: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
5226: update pa_bc_packets
5227: set entered_cr = l_entered_cr_tab(i),
5228: accounted_cr = l_accounted_cr_tab(i),
5229: entered_dr = 0,
5230: accounted_dr = 0
5234:
5235: --
5236: -- BUG 3703180
5237: -- Different line burdening setup.
5238: -- We determine the summary record from pa bc packets or pa bc commitments all table.
5239: -- compare the summary amounts with the pa bc packets burden and use the one less than
5240: -- the other. If pa bc packets burden cost is ZERO than we use the burden cost from the
5241: -- summary table record.
5242: --
5235: --
5236: -- BUG 3703180
5237: -- Different line burdening setup.
5238: -- We determine the summary record from pa bc packets or pa bc commitments all table.
5239: -- compare the summary amounts with the pa bc packets burden and use the one less than
5240: -- the other. If pa bc packets burden cost is ZERO than we use the burden cost from the
5241: -- summary table record.
5242: --
5243: elsif g_burden_type_tab(line_indx) = 'DIFFERENT' then
5236: -- BUG 3703180
5237: -- Different line burdening setup.
5238: -- We determine the summary record from pa bc packets or pa bc commitments all table.
5239: -- compare the summary amounts with the pa bc packets burden and use the one less than
5240: -- the other. If pa bc packets burden cost is ZERO than we use the burden cost from the
5241: -- summary table record.
5242: --
5243: elsif g_burden_type_tab(line_indx) = 'DIFFERENT' then
5244:
5245: l_exp_type_tab.delete ;
5246:
5247: --
5248: -- BUG 3703180
5249: -- Determine the burden expenditure type from pa bc packets.
5250: --
5251: select distinct pbc.expenditure_type
5252: bulk collect into l_exp_type_tab
5253: from pa_bc_packets pbc
5249: -- Determine the burden expenditure type from pa bc packets.
5250: --
5251: select distinct pbc.expenditure_type
5252: bulk collect into l_exp_type_tab
5253: from pa_bc_packets pbc
5254: where packet_id = p_packet_id
5255: and document_type = 'PO'
5256: and parent_bc_packet_id is not NULL
5257: and project_id = g_project_id_tab(line_indx)
5260:
5261: IF l_exp_type_tab.count > 0 THEN
5262: --
5263: -- BUG 3703180
5264: -- Determine the summary amounts from pa bc packets or pa bc commitments table.
5265: --
5266: for indx in 1..l_exp_type_tab.count loop
5267:
5268: g_bdamt_balance_tab.delete ;
5270: ** 3703180 : Determine the summary record amounts.. for a line
5271: */
5272: select (nvl(comm_tot_bd_amt,0) - nvl(comm_bd_amt_relieved,0) ) bd_amount
5273: bulk collect into g_bdamt_balance_tab
5274: from pa_bc_packets
5275: where document_type = 'PO'
5276: and summary_record_flag = 'Y'
5277: and document_line_id = g_doc_line_id_tab(line_indx)
5278: and status_code in ('A', 'C')
5311: l_accounted_cr_tab.delete ;
5312:
5313: --
5314: -- BUG 3703180
5315: -- Get the pa bc packets burden cost in the descending order
5316: --
5317: select bc_packet_id,
5318: entered_dr,
5319: entered_cr,
5323: l_entered_dr_tab,
5324: l_entered_cr_tab,
5325: l_accounted_dr_tab,
5326: l_accounted_cr_tab
5327: from pa_bc_packets
5328: where packet_id = p_packet_id
5329: and document_line_id = g_doc_line_id_tab(line_indx)
5330: and project_id = g_project_id_tab(line_indx)
5331: and task_id = g_task_id_tab(line_indx)
5371: end loop ;
5372:
5373: --
5374: -- BUG 3703180
5375: -- Update the burden cost to pa bc packets.
5376: --
5377: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
5378: update pa_bc_packets
5379: set entered_cr = l_entered_cr_tab(i),
5374: -- BUG 3703180
5375: -- Update the burden cost to pa bc packets.
5376: --
5377: FORALL i IN l_bc_packet_id_tab.first .. l_bc_packet_id_tab.last
5378: update pa_bc_packets
5379: set entered_cr = l_entered_cr_tab(i),
5380: accounted_cr = l_accounted_cr_tab(i),
5381: entered_dr = 0,
5382: accounted_dr = 0
5404:
5405:
5406: ---------------------------------------------------------------------
5407: -- this api creates the burden lines for the purchase order and
5408: -- supplier invoice lines in pa_bc_packets
5409: ---------------------------------------------------------------------
5410: FUNCTION create_ap_po_bdn_lines
5411: (p_packet_id IN NUMBER,
5412: p_bc_packet_id IN NUMBER,
5437: l_amount NUMBER ;
5438: l_prev_multiplier varchar2(1) ;
5439: l_tab_multiplier pa_plsql_datatypes.NumTabTyp ;
5440: l_tab_icc_exp_type pa_plsql_datatypes.char50TabTyp;
5441: l_doc_header_id pa_bc_packets.document_header_id%TYPE ;
5442: l_doc_distribution_id pa_bc_packets.document_distribution_id%TYPE ;
5443:
5444: /* Commented as part of Bug 5406690
5445: CURSOR C_ap_parent_reversal_dist IS --Bug 5515095
5438: l_prev_multiplier varchar2(1) ;
5439: l_tab_multiplier pa_plsql_datatypes.NumTabTyp ;
5440: l_tab_icc_exp_type pa_plsql_datatypes.char50TabTyp;
5441: l_doc_header_id pa_bc_packets.document_header_id%TYPE ;
5442: l_doc_distribution_id pa_bc_packets.document_distribution_id%TYPE ;
5443:
5444: /* Commented as part of Bug 5406690
5445: CURSOR C_ap_parent_reversal_dist IS --Bug 5515095
5446: SELECT parent_reversal_id
5475: (nvl(entered_dr,0) - NVL(entered_cr,0)) amount
5476: into l_doc_header_id,
5477: l_doc_distribution_id,
5478: l_amount
5479: from pa_bc_packets
5480: where packet_id = p_packet_id
5481: and bc_packet_id = p_bc_packet_id ;
5482:
5483: IF p_doc_type = 'AP' THEN
5542: pa_funds_control_pkg.log_message(p_msg_token1 =>'OLD Multiplier: pa_bc_commitments packet id '||l_max_packet_id);
5543:
5544: select max(packet_id)
5545: into l_max_packet_id_b
5546: from pa_bc_packets gbc1
5547: where packet_id <> p_packet_id
5548: and packet_id > NVL(l_max_packet_id,0)
5549: and document_type = p_doc_type
5550: and document_header_id = l_doc_header_id
5550: and document_header_id = l_doc_header_id
5551: and document_distribution_id = l_doc_distribution_id
5552: and status_code in ( 'A','C') ;
5553:
5554: pa_funds_control_pkg.log_message(p_msg_token1 =>'OLD Multiplier: pa_bc_packets packet id '||l_max_packet_id_b);
5555: -- bug : 3717214
5556: -- Spool the existing multipliers.
5557: BEGIN
5558: IF NVL(l_max_packet_id,0) >= nvl(l_max_packet_id_b,0) THEN
5566: and document_distribution_id = l_doc_distribution_id
5567: and document_type = p_doc_type
5568: -- and parent_bc_packet_id is not NULL ;
5569: -- Bug 5514074 : For AP with qty/amount variance there will be multiple RAW records in
5570: -- pa_bc_packets/bc commitments with same header_id and distribution_id.In such scenario
5571: -- we should pick burden lines against one of the raw line else it will result in burden
5572: -- duplication
5573: and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
5574: FROM pa_bc_commitments bc1
5585: select compiled_multiplier ,
5586: expenditure_type
5587: bulk collect into l_tab_multiplier,
5588: l_tab_icc_exp_type
5589: from pa_bc_packets
5590: where packet_id = l_max_packet_id_b
5591: and document_header_id = l_doc_header_id
5592: and document_distribution_id = l_doc_distribution_id
5593: and document_type = p_doc_type
5592: and document_distribution_id = l_doc_distribution_id
5593: and document_type = p_doc_type
5594: -- and parent_bc_packet_id is not NULL ;
5595: -- Bug 5514074 : For AP with qty/amount variance there will be multiple RAW records in
5596: -- pa_bc_packets/bc commitments with same header_id and distribution_id.In such scenario
5597: -- we should pick burden lines against one of the raw line else it will result in burden
5598: -- duplication
5599: and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
5600: FROM pa_bc_packets bc1
5596: -- pa_bc_packets/bc commitments with same header_id and distribution_id.In such scenario
5597: -- we should pick burden lines against one of the raw line else it will result in burden
5598: -- duplication
5599: and parent_bc_packet_id IN ( SELECT bc1.bc_packet_id -- SQL to fetch single raw record
5600: FROM pa_bc_packets bc1
5601: WHERE bc1.packet_id = l_max_packet_id_b
5602: AND bc1.document_header_id = l_doc_header_id
5603: AND bc1.document_distribution_id = l_doc_distribution_id
5604: AND bc1.document_type = p_doc_type
5604: AND bc1.document_type = p_doc_type
5605: AND bc1.parent_bc_packet_id IS NULL
5606: AND ROWNUM = 1 );
5607:
5608: pa_funds_control_pkg.log_message(p_msg_token1 =>'OLD Multiplier: pa_bc_packets multiplier used');
5609:
5610: END IF ;
5611: EXCEPTION
5612: when no_data_found then
5644: ), 0),
5645: expenditure_type
5646: BULK COLLECT into l_tab_multiplier ,
5647: l_tab_icc_exp_type
5648: FROM pa_bc_packets pbc
5649: WHERE pbc.packet_id = p_packet_id
5650: AND pbc.bc_packet_id = p_bc_packet_id
5651: AND pbc.document_type = p_doc_type
5652: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F'
5670: pa_cost_base_exp_types cbet,
5671: pa_ind_rate_schedules_all_bg irs,
5672: pa_ind_compiled_sets ics,
5673: pa_compiled_multipliers cm,
5674: pa_bc_packets pbc
5675: WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
5676: AND cb.cost_base = cbet.cost_base
5677: AND cb.cost_base_type = cbet.cost_base_type
5678: AND et.expenditure_type = icc.expenditure_type
5707: IF p_burden_type = 'SAME' THEN
5708: If p_related_link = 'N' then
5709: IF l_tab_multiplier.count > 0 THEN
5710: forall indx in 1..l_tab_multiplier.count
5711: INSERT INTO pa_bc_packets
5712: ( ---- who columns------
5713: request_id,
5714: program_id,
5715: program_application_id,
5782: sysdate,
5783: l_update_login,
5784: ------ main columns-----------
5785: pbc.packet_id,
5786: pa_bc_packets_s.nextval,
5787: pbc.budget_version_id,
5788: pbc.project_id,
5789: pbc.task_id,
5790: pbc.expenditure_type,
5857: ,pbc.ext_bdgt_flag
5858: ,pbc.document_distribution_type
5859: ,pbc.document_header_id_2
5860: ,pbc.proj_encumbrance_type_id
5861: FROM pa_bc_packets pbc
5862: WHERE pbc.packet_id = p_packet_id
5863: AND pbc.bc_packet_id = p_bc_packet_id
5864: AND pbc.document_type = p_doc_type
5865: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F' ;
5888: End if;
5889:
5890: END IF ; -- l_tab_multiplier.count endif
5891: Elsif p_related_link = 'Y' then
5892: INSERT INTO pa_bc_packets
5893: ( ---- who columns------
5894: request_id,
5895: program_id,
5896: program_application_id,
5962: sysdate,
5963: l_update_login,
5964: ------ main columns-----------
5965: pbc.packet_id,
5966: pa_bc_packets_s.nextval,
5967: pbc.budget_version_id,
5968: pbc.project_id,
5969: pbc.task_id,
5970: pbc.expenditure_type, --- p_exp_type
6039: pbc.ext_bdgt_flag,
6040: pbc.document_distribution_type,
6041: pbc.document_header_id_2,
6042: pbc.proj_encumbrance_type_id
6043: FROM pa_bc_packets pbc
6044: WHERE pbc.packet_id = p_packet_id
6045: AND pbc.bc_packet_id = p_bc_packet_id
6046: AND pbc.document_type = p_doc_type
6047: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
6074: ELSIF p_burden_type = 'DIFFERENT' then
6075: If p_related_link = 'N' then
6076: IF l_tab_multiplier.COUNT > 0 THEN
6077: forall indx in 1..l_tab_multiplier.COUNT
6078: INSERT INTO pa_bc_packets
6079: ( ---- who columns------
6080: request_id,
6081: program_id,
6082: program_application_id,
6149: sysdate,
6150: l_update_login,
6151: ------ main columns-----------
6152: pbc.packet_id,
6153: pa_bc_packets_s.nextval,
6154: pbc.budget_version_id,
6155: pbc.project_id,
6156: pbc.task_id,
6157: l_tab_icc_exp_type(indx),
6221: ,ext_bdgt_flag
6222: ,pbc.document_distribution_type
6223: ,pbc.document_header_id_2
6224: ,pbc.proj_encumbrance_type_id
6225: FROM pa_bc_packets pbc
6226: WHERE pbc.packet_id = p_packet_id
6227: AND pbc.bc_packet_id = p_bc_packet_id
6228: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
6229:
6255:
6256:
6257: END IF ; -- endif for l_tab_multiplier.COUNT
6258: Elsif p_related_link = 'Y' then
6259: INSERT INTO pa_bc_packets
6260: ( ---- who columns------
6261: request_id,
6262: program_id,
6263: program_application_id,
6329: sysdate,
6330: l_update_login,
6331: ------ main columns-----------
6332: pbc.packet_id,
6333: pa_bc_packets_s.nextval,
6334: pbc.budget_version_id,
6335: pbc.project_id,
6336: pbc.task_id,
6337: /* Bug fix:3026988
6412: ext_bdgt_flag,
6413: pbc.document_distribution_type,
6414: pbc.document_header_id_2,
6415: pbc.proj_encumbrance_type_id
6416: FROM pa_bc_packets pbc
6417: WHERE pbc.packet_id = p_packet_id
6418: AND pbc.bc_packet_id = p_bc_packet_id
6419: AND pbc.document_type = p_doc_type
6420: AND substr(nvl(pbc.result_code,'X'),1,1) <> 'F';
6455:
6456: END create_ap_po_bdn_lines;
6457:
6458: ------------------------------------------------------------------------------------------------------------------
6459: --This Api insert new records into pa bc packets if the project type is burdened.If the PO is based on REQ, or
6460: --Invoice is based on PO then it takes the burden amount for the REQ or PO from pa_bc_commitments table
6461: --and ensures that for req or po the old burden amount is used when reversing lines are passed in gl_bc_packets
6462: ------------------------------------------------------------------------------------------------------------------
6463: PROCEDURE Populate_burden_cost
6493: l_po_header_id NUMBER;
6494: l_status_flag VARCHAR2(1) := 'N';
6495: l_commitment_rows_flag VARCHAR2(10) := 'N';
6496: l_pkt_rows_flag VARCHAR2(10) := 'N';
6497: l_doc_header_id PA_BC_PACKETS.document_header_id%type;
6498: l_doc_distribution_id PA_BC_PACKETS.document_header_id%type;
6499: l_task_id NUMBER;
6500: l_ei_date date;
6501: l_base VARCHAR2(100);
6494: l_status_flag VARCHAR2(1) := 'N';
6495: l_commitment_rows_flag VARCHAR2(10) := 'N';
6496: l_pkt_rows_flag VARCHAR2(10) := 'N';
6497: l_doc_header_id PA_BC_PACKETS.document_header_id%type;
6498: l_doc_distribution_id PA_BC_PACKETS.document_header_id%type;
6499: l_task_id NUMBER;
6500: l_ei_date date;
6501: l_base VARCHAR2(100);
6502: l_cp_structure VARCHAR2(100);
6553: pktburd.expenditure_type,
6554: pktburd.compiled_multiplier
6555: FROM pa_bc_commitments_all pktburd
6556: ,pa_bc_commitments_all pktraw
6557: ,pa_bc_packets pkttrx
6558: WHERE pktburd.document_distribution_id = l_req_id
6559: AND pktburd.document_header_id = l_po_header_id
6560: AND pktburd.document_type = l_comm_doc_type
6561: AND pktburd.parent_bc_packet_id is NOT NULL
6576: AND pkttrx.packet_id = p_packet_id
6577: AND pkttrx.bc_packet_id = l_bc_packet_id;
6578:
6579:
6580: -- this cursor picks up the burden amount and details from pa_bc_packets table
6581: -- for the given distribution id ,document type and document header id
6582: -- the transactions which are approved but not yet swept
6583: CURSOR po_amount(l_req_id NUMBER,
6584: l_bc_packet_id NUMBER,
6621: * nvl(pkttrx.accounted_cr,0) accounted_cr,
6622: /* End : Bug 13911609*/
6623: pktburd.expenditure_type,
6624: pktburd.compiled_multiplier
6625: FROM pa_bc_packets pktburd
6626: ,pa_bc_packets pktraw
6627: ,pa_bc_packets pkttrx
6628: WHERE pktburd.document_distribution_id = l_req_id
6629: AND pktburd.document_header_id = l_po_header_id
6622: /* End : Bug 13911609*/
6623: pktburd.expenditure_type,
6624: pktburd.compiled_multiplier
6625: FROM pa_bc_packets pktburd
6626: ,pa_bc_packets pktraw
6627: ,pa_bc_packets pkttrx
6628: WHERE pktburd.document_distribution_id = l_req_id
6629: AND pktburd.document_header_id = l_po_header_id
6630: AND pktburd.document_type = l_pkt_doc_type
6623: pktburd.expenditure_type,
6624: pktburd.compiled_multiplier
6625: FROM pa_bc_packets pktburd
6626: ,pa_bc_packets pktraw
6627: ,pa_bc_packets pkttrx
6628: WHERE pktburd.document_distribution_id = l_req_id
6629: AND pktburd.document_header_id = l_po_header_id
6630: AND pktburd.document_type = l_pkt_doc_type
6631: AND pktburd.parent_bc_packet_id is NOT NULL
6633: AND pktburd.status_code in ('A','C')
6634: AND substr(nvl(pktburd.result_code,'P'),1,1) = 'P'
6635: AND (pktburd.packet_id,pktburd.parent_bc_packet_id) in
6636: ( SELECT MAX(pbc.packet_id),max(bc_packet_id)
6637: FROM pa_bc_packets pbc
6638: WHERE pbc.document_distribution_id = pktburd.document_distribution_id
6639: AND pbc.document_header_id = pktburd.document_header_id
6640: AND pbc.document_type = pktburd.document_type
6641: AND pbc.parent_bc_packet_id is NULL
6667: pbc.expenditure_type
6668: FROM pa_project_types ppt,
6669: pa_projects_all pp,
6670: pa_tasks ptk,
6671: pa_bc_packets pbc
6672: WHERE
6673: ppt.project_type = pp.project_type
6674: AND pp.project_id = pbc.project_id
6675: AND ptk.project_id = pbc.project_id
6777: IF l_burden_method = 'SAME' and l_bc_packet_id is NOT NULL then
6778:
6779: IF l_doc_type IN ( 'CC_C_PAY','CC_P_PAY','CC','CC_C_CO','CC_P_CO' ) THEN
6780: ----------------------------------------------------------------------------------
6781: --- insert into pa_bc_packets for the burden cost as a separate bc packet record
6782: --- after the funds check these records may be inserted into gl_bc_packets
6783: -----------------------------------------------------------------------------
6784: If l_parent_bc_packet_id = -1 then
6785: l_related_link := 'Y';
7439: END IF;
7440: IF p_calling_module in ('DISTBTC','TRXNIMPORT','DISTVIADJ','DISTERADJ','GL','TRXIMPORT','DISTCWKST') then
7441: -- update the bc packets set the parent bc packet id to null
7442: -- after derving the burden components for the raw lines
7443: UPDATE pa_bc_packets
7444: SET parent_bc_packet_id = null
7445: WHERE packet_id = p_packet_id
7446: AND parent_bc_packet_id = -1;
7447: END IF;
7527: -- ========================================
7528: PROCEDURE setup_start_end_date (
7529: p_packet_id IN NUMBER,
7530: p_bc_packet_id IN NUMBER,
7531: p_project_id IN pa_bc_packets.project_id%TYPE,
7532: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
7533: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7534: p_expenditure_item_date IN DATE,
7535: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7528: PROCEDURE setup_start_end_date (
7529: p_packet_id IN NUMBER,
7530: p_bc_packet_id IN NUMBER,
7531: p_project_id IN pa_bc_packets.project_id%TYPE,
7532: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
7533: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7534: p_expenditure_item_date IN DATE,
7535: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7536: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
7533: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
7534: p_expenditure_item_date IN DATE,
7535: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
7536: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
7537: p_set_of_books_id IN pa_bc_packets.set_of_books_id%TYPE,
7538: x_start_date OUT NOCOPY DATE,
7539: x_end_date OUT NOCOPY DATE,
7540: x_error_code OUT NOCOPY NUMBER,
7541: x_err_buff OUT NOCOPY VARCHAR2,
8070:
8071: FUNCTION get_start_or_end_date(
8072: p_packet_id IN NUMBER,
8073: p_bc_packet_id IN NUMBER,
8074: p_project_id IN pa_bc_packets.project_id%TYPE,
8075: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
8076: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
8077: p_expenditure_item_date IN DATE,
8078: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
8071: FUNCTION get_start_or_end_date(
8072: p_packet_id IN NUMBER,
8073: p_bc_packet_id IN NUMBER,
8074: p_project_id IN pa_bc_packets.project_id%TYPE,
8075: p_budget_version_id IN pa_bc_packets.budget_version_id%TYPE,
8076: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
8077: p_expenditure_item_date IN DATE,
8078: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
8079: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
8076: p_time_phase_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
8077: p_expenditure_item_date IN DATE,
8078: p_amount_type IN pa_budgetary_control_options.amount_type%TYPE,
8079: p_boundary_code IN pa_budgetary_control_options.boundary_code%TYPE,
8080: p_set_of_books_id IN pa_bc_packets.set_of_books_id%TYPE,
8081: p_type IN varchar2 -- START_DATE or END_DATE OR RESULT_CODE
8082: ) return DATE is
8083:
8084: l_start_date date;
8215: END LOOP;
8216: End if;
8217:
8218:
8219: UPDATE pa_bc_packets a
8220: SET status_code = DECODE(p_bc_mode,'C','F','R'),
8221: result_code = DECODE(substr(result_code,1,1),'F',result_code,'F172')
8222: WHERE status_code in ('P','I','A','S')
8223: AND source_event_id IN
8224: (SELECT event_id
8225: FROM PSA_BC_XLA_EVENTS_GT
8226: WHERE upper(result_code) in ('XLA_ERROR','FATAL','XLA_UNPROCESSED','XLA_NO_JOURNAL'));
8227:
8228: UPDATE pa_bc_packets a
8229: SET status_code = DECODE(p_bc_mode,'C','F','R'),
8230: result_code = DECODE(substr(result_code,1,1),'F',result_code,'F172')
8231: WHERE status_code in ('P','I','A','S')
8232: AND bc_event_id IN
8324:
8325: l_cdl_top_task_id pa_tasks.top_task_id%TYPE;
8326: l_cdl_budget_version_id pa_budget_versions.budget_version_id%TYPE;
8327: l_cdl_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE;
8328: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
8329: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
8330: l_error_message_code VARCHAR2(200) := NULL;
8331:
8332: -- Bug 5680236 : End of variable declaration
8325: l_cdl_top_task_id pa_tasks.top_task_id%TYPE;
8326: l_cdl_budget_version_id pa_budget_versions.budget_version_id%TYPE;
8327: l_cdl_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE;
8328: l_budget_line_id pa_bc_packets.budget_line_id%TYPE;
8329: l_budget_ccid pa_bc_packets.budget_ccid%TYPE;
8330: l_error_message_code VARCHAR2(200) := NULL;
8331:
8332: -- Bug 5680236 : End of variable declaration
8333:
8493: End if;
8494:
8495: -----------------------------------------------------------------------------------+
8496: -- Invoke the Sweeper process to sweep all the encumbrance etries
8497: -- from PA_BC_PACKETS to PA_BC_BALANCES and PA_BC_COMMITMENTS
8498: -----------------------------------------------------------------------------------+
8499: PA_Sweeper.Update_Act_Enc_Balance (
8500: X_Return_Status => l_Return_Status,
8501: X_Error_Message_Code => l_Msg_Data
8987: p_mode in varchar2,
8988: p_dr_cr in varchar2)
8989: return number
8990: is
8991: l_document_header_id pa_bc_packets.document_header_id%type;
8992: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8993: l_document_type pa_bc_packets.document_type%type;
8994: l_ratio pa_bc_packets.accounted_dr%type;
8995: Begin
8988: p_dr_cr in varchar2)
8989: return number
8990: is
8991: l_document_header_id pa_bc_packets.document_header_id%type;
8992: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8993: l_document_type pa_bc_packets.document_type%type;
8994: l_ratio pa_bc_packets.accounted_dr%type;
8995: Begin
8996: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
8989: return number
8990: is
8991: l_document_header_id pa_bc_packets.document_header_id%type;
8992: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8993: l_document_type pa_bc_packets.document_type%type;
8994: l_ratio pa_bc_packets.accounted_dr%type;
8995: Begin
8996: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
8997: pa_funds_control_pkg.log_message(p_msg_token1 => 'Inside get_ratio');
8990: is
8991: l_document_header_id pa_bc_packets.document_header_id%type;
8992: l_document_distribution_id pa_bc_packets.document_distribution_id%type;
8993: l_document_type pa_bc_packets.document_type%type;
8994: l_ratio pa_bc_packets.accounted_dr%type;
8995: Begin
8996: IF pa_funds_control_pkg.g_debug_mode = 'Y' THEN
8997: pa_funds_control_pkg.log_message(p_msg_token1 => 'Inside get_ratio');
8998: pa_funds_control_pkg.log_message(p_msg_token1 => 'Value of p_mode is= '||p_mode);
9016: decode(p_dr_cr,'A',abs(nvl(accounted_dr,0)-nvl(accounted_cr,0)),
9017: 'E',abs(nvl(entered_dr,0)-nvl(entered_cr,0)))
9018: ,0))
9019: INTO l_ratio
9020: FROM pa_bc_packets pbc1
9021: WHERE pbc1.packet_id = ( SELECT max(pbc.packet_id)
9022: FROM pa_bc_packets pbc
9023: WHERE pbc.document_distribution_id = l_document_distribution_id
9024: AND pbc.document_header_id = l_document_header_id
9018: ,0))
9019: INTO l_ratio
9020: FROM pa_bc_packets pbc1
9021: WHERE pbc1.packet_id = ( SELECT max(pbc.packet_id)
9022: FROM pa_bc_packets pbc
9023: WHERE pbc.document_distribution_id = l_document_distribution_id
9024: AND pbc.document_header_id = l_document_header_id
9025: AND pbc.document_type = l_document_type
9026: AND pbc.parent_bc_packet_id is NULL
9249:
9250: --Get the accounting currency into a global variable.
9251: g_acct_currency_code := pa_multi_currency.g_accounting_currency_code;
9252:
9253: --insert these records into pa bc packets
9254: pa_funds_control_pkg.log_message
9255: (p_msg_token1 => 'calling create pkt lines api');
9256:
9257: create_CBC_pkt_lines
9273:
9274: --Get the accounting currency into a global variable.
9275: g_acct_currency_code := pa_multi_currency.g_accounting_currency_code;
9276:
9277: --insert these records into pa bc packets
9278: pa_funds_control_pkg.log_message
9279: (p_msg_token1 => 'calling create pkt lines api');
9280:
9281: create_CBC_pkt_lines
9339: NULL distribution_type,
9340: NULL po_release_Id,
9341: PA_FUNDS_CONTROL_UTILS.get_encum_type_id(igc.project_id,'STD') enc_type_id,
9342: igchead.vendor_id,
9343: pa_bc_packets_s.nextval
9344: FROM pa_tasks pkt,
9345: pa_projects_all pp,
9346: igc_cc_acct_lines igc,
9347: igc_cc_det_pf igcpf,
9422: 'Provisional','CC_P_CO')
9423: pkt_reference1,
9424: igci.cc_header_id pkt_reference2,
9425: igci.cc_acct_line_id pkt_reference3,
9426: pa_bc_packets_s.nextval
9427: FROM
9428: pa_tasks pkt,
9429: pa_projects_all pp,
9430: igc_cc_interface igci,