[Home] [Help]
1: Package BODY pa_billing_pub AS
2: /* $Header: PAXIPUBB.pls 120.6.12020000.2 2012/07/19 10:05:00 admarath ship $ */
3:
4: --------------------------------------
5: -- PROCEDURE/FUNCTION IMPLEMENTATIONS
67: cost_budget_not_baselined EXCEPTION;
68:
69: BEGIN
70: IF g1_debug_mode = 'Y' THEN
71: PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.get_budget_amount: ');
72: END IF;
73: X_status := 0;
74: X_error_message := NULL;
75: BEGIN
89: INTO l_cost_budget_type_code,
90: l_rev_budget_type_code,
91: l_cost_plan_type_id,
92: l_rev_plan_type_id
93: FROM pa_billing_extensions
94: WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
95:
96: END IF;
97:
90: l_rev_budget_type_code,
91: l_cost_plan_type_id,
92: l_rev_plan_type_id
93: FROM pa_billing_extensions
94: WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
95:
96: END IF;
97:
98:
274:
275: -- If any exception then raise it to the calling pl/sql block
276: --
277: IF g1_debug_mode = 'Y' THEN
278: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.get_budget_amount: ');
279: END IF;
280: EXCEPTION
281: WHEN invalid_cost_budget_code THEN
282: status := pa_billing_values.get_message('INVALID_COST_BUDGET_TYPE');
278: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.get_budget_amount: ');
279: END IF;
280: EXCEPTION
281: WHEN invalid_cost_budget_code THEN
282: status := pa_billing_values.get_message('INVALID_COST_BUDGET_TYPE');
283: l_status := 1;
284: RAISE_APPLICATION_ERROR(-20101,status);
285: WHEN invalid_rev_budget_code THEN
286: status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
282: status := pa_billing_values.get_message('INVALID_COST_BUDGET_TYPE');
283: l_status := 1;
284: RAISE_APPLICATION_ERROR(-20101,status);
285: WHEN invalid_rev_budget_code THEN
286: status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
287: l_status := 2;
288: RAISE_APPLICATION_ERROR(-20101,status);
289: WHEN rev_budget_not_baselined THEN
290: status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
286: status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
287: l_status := 2;
288: RAISE_APPLICATION_ERROR(-20101,status);
289: WHEN rev_budget_not_baselined THEN
290: status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
291: l_status := 3;
292: RAISE_APPLICATION_ERROR(-20101,status);
293: WHEN cost_budget_not_baselined THEN
294: status := pa_billing_values.get_message('COST_BUDGET_NOT_BASELINED');
290: status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
291: l_status := 3;
292: RAISE_APPLICATION_ERROR(-20101,status);
293: WHEN cost_budget_not_baselined THEN
294: status := pa_billing_values.get_message('COST_BUDGET_NOT_BASELINED');
295: l_status := 4;
296: RAISE_APPLICATION_ERROR(-20101,status);
297: WHEN OTHERS THEN
298: status := substr(SQLERRM,1,240);
313: X_error_message := status;
314: X_status := l_status;
315:
316: insert_message
317: (X_inserting_procedure_name =>'pa_billing_pub.get_budget_amount',
318: X_attribute1 => l_cost_budget_type_code,
319: X_attribute2 => l_rev_budget_type_code,
320: X_message => status,
321: X_error_message=>err_msg,
493:
494: BEGIN
495:
496: IF g1_debug_mode = 'Y' THEN
497: PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.get_amount: ');
498: END IF;
499: total_amount := 0;
500:
501: IF (X_Request_id IS NULL) THEN -->No RqstId given
502:
503: IF (X_which_amount = 'I' OR X_which_amount = 'R') THEN -->Want Inv/Rev amts
504: FOR Proj_rec IN ByProject LOOP
505: total_amount := total_amount +
506: nvl(pa_billing_amount.rdl_amount(X_which_amount,
507: Proj_rec.eid, X_calling_place, Proj_rec.ei_adj),0);
508: END LOOP;
509: ELSE -->Want cost amounts
510: FOR Proj_rec IN ByProject LOOP
529: END IF;
530: X_amount := pa_currency.round_currency_amt(total_amount);
531:
532: IF g1_debug_mode = 'Y' THEN
533: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.get_amount: ');
534: END IF;
535: EXCEPTION
536: WHEN NO_DATA_FOUND THEN
537: -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
574: xo_line_num NUMBER(15);
575:
576: BEGIN
577: IF g1_debug_mode = 'Y' THEN
578: PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.insert_message: ');
579: END IF;
580: x_created_by := FND_GLOBAL.USER_ID;
581: x_last_updated_by := FND_GLOBAL.USER_ID;
582: x_last_update_login := FND_GLOBAL.LOGIN_ID;
587: X_error_message := NULL;
588:
589: SELECT max(BM.line_num)
590: INTO xo_line_num
591: FROM PA_BILLING_MESSAGES BM
592: WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593: AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594: AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595: AND BM.calling_process = pa_billing.GlobVars.CallingProcess
588:
589: SELECT max(BM.line_num)
590: INTO xo_line_num
591: FROM PA_BILLING_MESSAGES BM
592: WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593: AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594: AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595: AND BM.calling_process = pa_billing.GlobVars.CallingProcess
596: AND BM.request_id = pa_billing.GlobVars.ReqId;
589: SELECT max(BM.line_num)
590: INTO xo_line_num
591: FROM PA_BILLING_MESSAGES BM
592: WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593: AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594: AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595: AND BM.calling_process = pa_billing.GlobVars.CallingProcess
596: AND BM.request_id = pa_billing.GlobVars.ReqId;
597:
590: INTO xo_line_num
591: FROM PA_BILLING_MESSAGES BM
592: WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593: AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594: AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595: AND BM.calling_process = pa_billing.GlobVars.CallingProcess
596: AND BM.request_id = pa_billing.GlobVars.ReqId;
597:
598: IF (xo_line_num IS NULL) THEN
591: FROM PA_BILLING_MESSAGES BM
592: WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593: AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594: AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595: AND BM.calling_process = pa_billing.GlobVars.CallingProcess
596: AND BM.request_id = pa_billing.GlobVars.ReqId;
597:
598: IF (xo_line_num IS NULL) THEN
599: xo_line_num := 1;
592: WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593: AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594: AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595: AND BM.calling_process = pa_billing.GlobVars.CallingProcess
596: AND BM.request_id = pa_billing.GlobVars.ReqId;
597:
598: IF (xo_line_num IS NULL) THEN
599: xo_line_num := 1;
600: ELSE xo_line_num := xo_line_num + 1;
599: xo_line_num := 1;
600: ELSE xo_line_num := xo_line_num + 1;
601: END IF;
602:
603: INSERT INTO PA_BILLING_MESSAGES
604: (inserting_procedure_name,
605: Billing_Assignment_Id,
606: Project_Id,
607: Task_Id,
631: attribute14,
632: attribute15)
633: VALUES (
634: X_inserting_procedure_name,
635: pa_billing.GlobVars.BillingAssignmentId,
636: pa_billing.GlobVars.ProjectId,
637: pa_billing.GlobVars.TaskId,
638: pa_billing.GlobVars.CallingPlace,
639: pa_billing.GlobVars.CallingProcess,
632: attribute15)
633: VALUES (
634: X_inserting_procedure_name,
635: pa_billing.GlobVars.BillingAssignmentId,
636: pa_billing.GlobVars.ProjectId,
637: pa_billing.GlobVars.TaskId,
638: pa_billing.GlobVars.CallingPlace,
639: pa_billing.GlobVars.CallingProcess,
640: pa_billing.GlobVars.ReqId,
633: VALUES (
634: X_inserting_procedure_name,
635: pa_billing.GlobVars.BillingAssignmentId,
636: pa_billing.GlobVars.ProjectId,
637: pa_billing.GlobVars.TaskId,
638: pa_billing.GlobVars.CallingPlace,
639: pa_billing.GlobVars.CallingProcess,
640: pa_billing.GlobVars.ReqId,
641: xo_line_num,
634: X_inserting_procedure_name,
635: pa_billing.GlobVars.BillingAssignmentId,
636: pa_billing.GlobVars.ProjectId,
637: pa_billing.GlobVars.TaskId,
638: pa_billing.GlobVars.CallingPlace,
639: pa_billing.GlobVars.CallingProcess,
640: pa_billing.GlobVars.ReqId,
641: xo_line_num,
642: X_message,
635: pa_billing.GlobVars.BillingAssignmentId,
636: pa_billing.GlobVars.ProjectId,
637: pa_billing.GlobVars.TaskId,
638: pa_billing.GlobVars.CallingPlace,
639: pa_billing.GlobVars.CallingProcess,
640: pa_billing.GlobVars.ReqId,
641: xo_line_num,
642: X_message,
643: sysdate,
636: pa_billing.GlobVars.ProjectId,
637: pa_billing.GlobVars.TaskId,
638: pa_billing.GlobVars.CallingPlace,
639: pa_billing.GlobVars.CallingProcess,
640: pa_billing.GlobVars.ReqId,
641: xo_line_num,
642: X_message,
643: sysdate,
644: x_created_by,
663:
664: --commit;
665:
666: IF g1_debug_mode = 'Y' THEN
667: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_message: ');
668: END IF;
669: EXCEPTION
670: WHEN OTHERS THEN
671: -- DBMS_OUTPUT.PUT(SQLERRM);
806:
807: BEGIN
808: BEGIN
809: IF g1_debug_mode = 'Y' THEN
810: PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.insert_event:' );
811: END IF;
812: -- Assigning who columns for insertion into PA_EVENTS.
813:
814: created_by := FND_GLOBAL.USER_ID;
819:
820: X_status := 0;
821: X_error_message := NULL;
822: -- Validate Mandatory Parameters
823: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
824: pa_billing.GlobVars.ReqId IS NULL OR
825: pa_billing.GlobVars.CallingPlace IS NULL OR
826: pa_billing.GlobVars.CallingProcess IS NULL) THEN
827: raise mandatory_prm_missing;
820: X_status := 0;
821: X_error_message := NULL;
822: -- Validate Mandatory Parameters
823: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
824: pa_billing.GlobVars.ReqId IS NULL OR
825: pa_billing.GlobVars.CallingPlace IS NULL OR
826: pa_billing.GlobVars.CallingProcess IS NULL) THEN
827: raise mandatory_prm_missing;
828: END IF;
821: X_error_message := NULL;
822: -- Validate Mandatory Parameters
823: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
824: pa_billing.GlobVars.ReqId IS NULL OR
825: pa_billing.GlobVars.CallingPlace IS NULL OR
826: pa_billing.GlobVars.CallingProcess IS NULL) THEN
827: raise mandatory_prm_missing;
828: END IF;
829:
822: -- Validate Mandatory Parameters
823: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
824: pa_billing.GlobVars.ReqId IS NULL OR
825: pa_billing.GlobVars.CallingPlace IS NULL OR
826: pa_billing.GlobVars.CallingProcess IS NULL) THEN
827: raise mandatory_prm_missing;
828: END IF;
829:
830: /* The following logic has been added for MCB2 functionality */
827: raise mandatory_prm_missing;
828: END IF;
829:
830: /* The following logic has been added for MCB2 functionality */
831: l_project_id := nvl(X_project_id, pa_billing.GlobVars.ProjectId);
832: PA_MULTI_CURRENCY_BILLING.get_project_defaults (
833: p_project_id => l_project_id,
834: x_multi_currency_billing_flag => l_multi_currency_billing_flag,
835: x_baseline_funding_flag => l_baseline_funding_flag,
871:
872: /* Rounding the transaction amount upto the precision of transaction currency for MCB2 */
873: XD_bill_trans_rev_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_rev_amt, 0),l_txn_currency_code);
874: IF g1_debug_mode = 'Y' THEN
875: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: rev amt'||to_char(XD_bill_trans_rev_amt));
876: END IF;
877: XD_bill_trans_bill_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_bill_amt,0),l_txn_currency_code);
878: IF g1_debug_mode = 'Y' THEN
879: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: inv amt'||to_char(XD_bill_trans_bill_amt));
875: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: rev amt'||to_char(XD_bill_trans_rev_amt));
876: END IF;
877: XD_bill_trans_bill_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_bill_amt,0),l_txn_currency_code);
878: IF g1_debug_mode = 'Y' THEN
879: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: inv amt'||to_char(XD_bill_trans_bill_amt));
880: END IF;
881: /* The following amounts have been commented for MCB2, the above amounts are satisfying the same requirement */
882: /* XD_bill_trans_rev_amt := (NVL(X_rev_amt, 0));
883: XD_bill_trans_bill_amt := (NVL(X_bill_amt,0)); */
883: XD_bill_trans_bill_amt := (NVL(X_bill_amt,0)); */
884:
885: -- Get defaults for other non-mandatory parameters
886: XD_completion_date := nvl(X_completion_date,
887: to_date(pa_billing.GlobVars.AccrueThruDate,'YYYY/MM/DD'));
888:
889:
890: -- Added check to prevent event creation from other calling places like 'PRE' 'POST'
891: -- 'DEL' etc.
889:
890: -- Added check to prevent event creation from other calling places like 'PRE' 'POST'
891: -- 'DEL' etc.
892: --
893: IF (pa_billing.GlobVars.CallingPlace NOT IN ('REG','ADJ','POST-REG')) THEN
894: raise invalid_calling_place;
895: END IF;
896:
897:
895: END IF;
896:
897:
898: IF (X_organization_id IS NULL) THEN
899: XD_organization_id := pa_billing_values.get_dflt_org(
900: nvl(X_project_id,
901: pa_billing.GlobVars.ProjectId),
902: nvl(X_top_task_id,
903: pa_billing.GlobVars.TaskId));
897:
898: IF (X_organization_id IS NULL) THEN
899: XD_organization_id := pa_billing_values.get_dflt_org(
900: nvl(X_project_id,
901: pa_billing.GlobVars.ProjectId),
902: nvl(X_top_task_id,
903: pa_billing.GlobVars.TaskId));
904: ELSE
905: XD_organization_id := X_organization_id;
899: XD_organization_id := pa_billing_values.get_dflt_org(
900: nvl(X_project_id,
901: pa_billing.GlobVars.ProjectId),
902: nvl(X_top_task_id,
903: pa_billing.GlobVars.TaskId));
904: ELSE
905: XD_organization_id := X_organization_id;
906: END IF;
907:
906: END IF;
907:
908: IF (X_event_description IS NULL OR
909: X_event_type IS NULL) THEN
910: pa_billing_values.get_dflt_desc(pa_billing.GlobVars.BillingAssignmentId,
911: XD_event_type, XD_event_description);
912: XD_event_description := nvl(X_event_description,
913: XD_event_description);
914: XD_event_type := nvl(X_event_type, XD_event_type);
923:
924:
925: -- Validate Id's
926:
927: IF (pa_billing_validate.valid_proj_task_extn(
928: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
929: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
930: pa_billing.GlobVars.BillingAssignmentId) AND
931: pa_billing_validate.valid_organization(XD_organization_id)) THEN
924:
925: -- Validate Id's
926:
927: IF (pa_billing_validate.valid_proj_task_extn(
928: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
929: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
930: pa_billing.GlobVars.BillingAssignmentId) AND
931: pa_billing_validate.valid_organization(XD_organization_id)) THEN
932: NULL;
925: -- Validate Id's
926:
927: IF (pa_billing_validate.valid_proj_task_extn(
928: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
929: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
930: pa_billing.GlobVars.BillingAssignmentId) AND
931: pa_billing_validate.valid_organization(XD_organization_id)) THEN
932: NULL;
933: ELSE
926:
927: IF (pa_billing_validate.valid_proj_task_extn(
928: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
929: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
930: pa_billing.GlobVars.BillingAssignmentId) AND
931: pa_billing_validate.valid_organization(XD_organization_id)) THEN
932: NULL;
933: ELSE
934: RAISE INVALID_ID;
927: IF (pa_billing_validate.valid_proj_task_extn(
928: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
929: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
930: pa_billing.GlobVars.BillingAssignmentId) AND
931: pa_billing_validate.valid_organization(XD_organization_id)) THEN
932: NULL;
933: ELSE
934: RAISE INVALID_ID;
935: END IF;
937:
938:
939: -- Validate funding level
940:
941: IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
942: IF (pa_billing_values.funding_level(nvl(X_project_id,
943: pa_billing.GlobVars.ProjectId))
944: <> 'PROJECT') THEN
945: RAISE invalid_project_event;
938:
939: -- Validate funding level
940:
941: IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
942: IF (pa_billing_values.funding_level(nvl(X_project_id,
943: pa_billing.GlobVars.ProjectId))
944: <> 'PROJECT') THEN
945: RAISE invalid_project_event;
946: END IF;
939: -- Validate funding level
940:
941: IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
942: IF (pa_billing_values.funding_level(nvl(X_project_id,
943: pa_billing.GlobVars.ProjectId))
944: <> 'PROJECT') THEN
945: RAISE invalid_project_event;
946: END IF;
947: END IF;
953: -- Validate amounts based on which process is calling.
954: IF ((((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N')) AND
955: (XD_bill_trans_bill_amt = 0)) OR
956: ((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N') AND
957: pa_billing.GlobVars.CallingProcess = 'Revenue') OR
958: ((XD_bill_trans_bill_amt = 0) AND
959: pa_billing.GlobVars.CallingProcess = 'Invoice')) THEN
960: RAISE zero_amounts;
961: END IF;
955: (XD_bill_trans_bill_amt = 0)) OR
956: ((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N') AND
957: pa_billing.GlobVars.CallingProcess = 'Revenue') OR
958: ((XD_bill_trans_bill_amt = 0) AND
959: pa_billing.GlobVars.CallingProcess = 'Invoice')) THEN
960: RAISE zero_amounts;
961: END IF;
962:
963: /* Modified the below condition for Bug 9154825 */
961: END IF;
962:
963: /* Modified the below condition for Bug 9154825 */
964: -- Check original event num for the ADJ automatic events.
965: IF (PA_BILLING.GetCallPlace = 'ADJ' AND
966: (pa_billing.GlobVars.CallingProcess = 'Invoice' OR pa_billing.GlobVars.CallingProcess = 'Revenue') AND
967: nvl(X_event_num_reversed, 0) = 0) THEN
968: RAISE no_orig_event;
969: END IF;
962:
963: /* Modified the below condition for Bug 9154825 */
964: -- Check original event num for the ADJ automatic events.
965: IF (PA_BILLING.GetCallPlace = 'ADJ' AND
966: (pa_billing.GlobVars.CallingProcess = 'Invoice' OR pa_billing.GlobVars.CallingProcess = 'Revenue') AND
967: nvl(X_event_num_reversed, 0) = 0) THEN
968: RAISE no_orig_event;
969: END IF;
970:
967: nvl(X_event_num_reversed, 0) = 0) THEN
968: RAISE no_orig_event;
969: END IF;
970:
971: event_num := pa_billing_seq.next_eventnum(
972: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
973: nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
974:
975:
968: RAISE no_orig_event;
969: END IF;
970:
971: event_num := pa_billing_seq.next_eventnum(
972: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
973: nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
974:
975:
976: /* Adding validation of newly added columns in event table for project contract integration */
969: END IF;
970:
971: event_num := pa_billing_seq.next_eventnum(
972: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
973: nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
974:
975:
976: /* Adding validation of newly added columns in event table for project contract integration */
977: -- Validating inventory_org_id and inventory_item_id
976: /* Adding validation of newly added columns in event table for project contract integration */
977: -- Validating inventory_org_id and inventory_item_id
978:
979: IF (x_inventory_org_id IS NOT NULL) THEN
980: IF (pa_billing_validate.valid_organization(x_inventory_org_id)) THEN
981: NULL;
982: ELSE
983: RAISE INVALID_INVENT_ID;
984: END IF;
1208: l_revproc_exchange_rate := l_projfunc_bil_exchange_rate;
1209: END IF;
1210:
1211: /* MCB2: Removed Revenue amount and Bill amount because,these amounts are being used only for transactions */
1212: IF (pa_billing_validate.automatic_event(XD_event_type)) THEN
1213: insert into pa_events
1214: (PROJECT_ID, TASK_ID, ORGANIZATION_ID, EVENT_NUM, EVENT_TYPE, -- 1
1215: REVENUE_AMOUNT,BILL_AMOUNT,COMPLETION_DATE, REQUEST_ID, -- 2
1216: DESCRIPTION, BILL_HOLD_FLAG, REV_DIST_REJECTION_CODE, -- 3
1275: AUDIT_COST_PLAN_TYPE_ID, /* Added for Fin Plan impact */
1276: AUDIT_REV_PLAN_TYPE_ID /* Added for Fin Plan impact */
1277: )
1278: values
1279: (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
1280: XD_organization_id, event_num, XD_event_type, -- 1
1281: 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
1282: XD_event_description, 'N', NULL, -- 3
1283: 'N', program_application_id,program_id, -- 4
1277: )
1278: values
1279: (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
1280: XD_organization_id, event_num, XD_event_type, -- 1
1281: 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
1282: XD_event_description, 'N', NULL, -- 3
1283: 'N', program_application_id,program_id, -- 4
1284: sysdate, sysdate, nvl(last_updated_by,0), -- 5
1285: sysdate, nvl(created_by,0), nvl(last_update_login,0),
1287: X_attribute1, X_attribute2, X_attribute3, X_attribute4,
1288: X_attribute5, -- 7
1289: X_attribute6, X_attribute7, X_attribute8, X_attribute9,
1290: X_attribute10, -- 8
1291: pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
1292: pa_billing.GlobVars.CallingProcess, -- 9
1293: X_event_num_reversed, -- 10
1294: pa_currency.round_currency_amt(X_audit_amount1),
1295: pa_currency.round_currency_amt(X_audit_amount2),
1288: X_attribute5, -- 7
1289: X_attribute6, X_attribute7, X_attribute8, X_attribute9,
1290: X_attribute10, -- 8
1291: pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
1292: pa_billing.GlobVars.CallingProcess, -- 9
1293: X_event_num_reversed, -- 10
1294: pa_currency.round_currency_amt(X_audit_amount1),
1295: pa_currency.round_currency_amt(X_audit_amount2),
1296: pa_currency.round_currency_amt(X_audit_amount3),
1349: END IF;
1350: -- COMMIT;
1351:
1352: IF g1_debug_mode = 'Y' THEN
1353: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_events: ');
1354: END IF;
1355: EXCEPTION
1356: WHEN mandatory_prm_missing THEN
1357: status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
1353: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_events: ');
1354: END IF;
1355: EXCEPTION
1356: WHEN mandatory_prm_missing THEN
1357: status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
1358: l_status := 1;
1359: RAISE;
1360: WHEN invalid_id THEN
1361: status := pa_billing_values.get_message('INVALID_ID');
1357: status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
1358: l_status := 1;
1359: RAISE;
1360: WHEN invalid_id THEN
1361: status := pa_billing_values.get_message('INVALID_ID');
1362: l_status := 2;
1363: RAISE;
1364: WHEN invalid_project_event THEN
1365: status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
1361: status := pa_billing_values.get_message('INVALID_ID');
1362: l_status := 2;
1363: RAISE;
1364: WHEN invalid_project_event THEN
1365: status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
1366: l_status := 3;
1367: RAISE;
1368: /*WHEN event_type_error THEN commenting this for bug 3492506
1369: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1365: status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
1366: l_status := 3;
1367: RAISE;
1368: /*WHEN event_type_error THEN commenting this for bug 3492506
1369: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1370: RAISE;*/
1371: WHEN null_event_type_error THEN
1372: status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
1373: l_status := 4;
1368: /*WHEN event_type_error THEN commenting this for bug 3492506
1369: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1370: RAISE;*/
1371: WHEN null_event_type_error THEN
1372: status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
1373: l_status := 4;
1374: RAISE;
1375: WHEN zero_amounts THEN
1376: status := pa_billing_values.get_message('ZERO_AMOUNTS');
1372: status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
1373: l_status := 4;
1374: RAISE;
1375: WHEN zero_amounts THEN
1376: status := pa_billing_values.get_message('ZERO_AMOUNTS');
1377: l_status := 5;
1378: RAISE;
1379: WHEN no_orig_event THEN
1380: status := pa_billing_values.get_message('NO_ORIG_EVENT');
1376: status := pa_billing_values.get_message('ZERO_AMOUNTS');
1377: l_status := 5;
1378: RAISE;
1379: WHEN no_orig_event THEN
1380: status := pa_billing_values.get_message('NO_ORIG_EVENT');
1381: l_status := 6;
1382: RAISE;
1383: WHEN invalid_calling_place THEN
1384: status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
1380: status := pa_billing_values.get_message('NO_ORIG_EVENT');
1381: l_status := 6;
1382: RAISE;
1383: WHEN invalid_calling_place THEN
1384: status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
1385: l_status := 7;
1386: RAISE;
1387: WHEN invalid_invent_id THEN
1388: status := pa_billing_values.get_message('INVALID_INVENT_ID');
1384: status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
1385: l_status := 7;
1386: RAISE;
1387: WHEN invalid_invent_id THEN
1388: status := pa_billing_values.get_message('INVALID_INVENT_ID');
1389: l_status := 8;
1390: RAISE;
1391: WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
1392: status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
1388: status := pa_billing_values.get_message('INVALID_INVENT_ID');
1389: l_status := 8;
1390: RAISE;
1391: WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
1392: status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
1393: l_status := 9;
1394: RAISE;
1395: WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
1396: status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
1392: status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
1393: l_status := 9;
1394: RAISE;
1395: WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
1396: status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
1397: l_status := 10;
1398: RAISE;
1399: WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
1400: status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
1396: status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
1397: l_status := 10;
1398: RAISE;
1399: WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
1400: status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
1401: l_status := 11;
1402: RAISE;
1403: WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
1404: status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
1400: status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
1401: l_status := 11;
1402: RAISE;
1403: WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
1404: status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
1405: l_status := 12;
1406: RAISE;
1407: WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
1408: status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
1404: status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
1405: l_status := 12;
1406: RAISE;
1407: WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
1408: status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
1409: l_status := 13;
1410: RAISE;
1411: WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
1412: status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
1408: status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
1409: l_status := 13;
1410: RAISE;
1411: WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
1412: status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
1413: l_status := 14;
1414: RAISE;
1415: WHEN l_invalid_currency THEN /* Added for MCB2 */
1416: status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
1412: status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
1413: l_status := 14;
1414: RAISE;
1415: WHEN l_invalid_currency THEN /* Added for MCB2 */
1416: status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
1417: l_status := 15;
1418: RAISE;
1419: WHEN event_type_error THEN /* adding this here for bug 3492506 */
1420: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1416: status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
1417: l_status := 15;
1418: RAISE;
1419: WHEN event_type_error THEN /* adding this here for bug 3492506 */
1420: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1421: l_status := 16;
1422: RAISE;
1423: WHEN OTHERS THEN
1424: status := substr(SQLERRM,1,240);
1432: -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1433: X_error_message := status;
1434: X_status := l_status;
1435:
1436: insert_message(X_inserting_procedure_name => 'pa_billing_pub.insert_event',
1437: X_attribute1 => XD_bill_trans_rev_amt,
1438: X_attribute2 => XD_bill_trans_bill_amt,
1439: X_message => status,
1440: X_status => err_status,
1645:
1646: BEGIN
1647: BEGIN
1648: IF g1_debug_mode = 'Y' THEN
1649: PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.insert_event2:' );
1650: END IF;
1651: -- Assigning who columns for insertion into PA_EVENTS.
1652:
1653: created_by := FND_GLOBAL.USER_ID;
1658:
1659: X_status := 0;
1660: X_error_message := NULL;
1661: -- Validate Mandatory Parameters
1662: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
1663: pa_billing.GlobVars.ReqId IS NULL OR
1664: pa_billing.GlobVars.CallingPlace IS NULL OR
1665: pa_billing.GlobVars.CallingProcess IS NULL) THEN
1666: raise mandatory_prm_missing;
1659: X_status := 0;
1660: X_error_message := NULL;
1661: -- Validate Mandatory Parameters
1662: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
1663: pa_billing.GlobVars.ReqId IS NULL OR
1664: pa_billing.GlobVars.CallingPlace IS NULL OR
1665: pa_billing.GlobVars.CallingProcess IS NULL) THEN
1666: raise mandatory_prm_missing;
1667: END IF;
1660: X_error_message := NULL;
1661: -- Validate Mandatory Parameters
1662: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
1663: pa_billing.GlobVars.ReqId IS NULL OR
1664: pa_billing.GlobVars.CallingPlace IS NULL OR
1665: pa_billing.GlobVars.CallingProcess IS NULL) THEN
1666: raise mandatory_prm_missing;
1667: END IF;
1668:
1661: -- Validate Mandatory Parameters
1662: IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
1663: pa_billing.GlobVars.ReqId IS NULL OR
1664: pa_billing.GlobVars.CallingPlace IS NULL OR
1665: pa_billing.GlobVars.CallingProcess IS NULL) THEN
1666: raise mandatory_prm_missing;
1667: END IF;
1668:
1669: /* The following logic has been added for MCB2 functionality */
1666: raise mandatory_prm_missing;
1667: END IF;
1668:
1669: /* The following logic has been added for MCB2 functionality */
1670: l_project_id := nvl(X_project_id, pa_billing.GlobVars.ProjectId);
1671: PA_MULTI_CURRENCY_BILLING.get_project_defaults (
1672: p_project_id => l_project_id,
1673: x_multi_currency_billing_flag => l_multi_currency_billing_flag,
1674: x_baseline_funding_flag => l_baseline_funding_flag,
1710:
1711: /* Rounding the transaction amount upto the precision of transaction currency for MCB2 */
1712: XD_bill_trans_rev_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_rev_amt, 0),l_txn_currency_code);
1713: IF g1_debug_mode = 'Y' THEN
1714: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event2: rev amt'||to_char(XD_bill_trans_rev_amt));
1715: END IF;
1716: XD_bill_trans_bill_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_bill_amt,0),l_txn_currency_code);
1717: IF g1_debug_mode = 'Y' THEN
1718: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event2: inv amt'||to_char(XD_bill_trans_bill_amt));
1714: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event2: rev amt'||to_char(XD_bill_trans_rev_amt));
1715: END IF;
1716: XD_bill_trans_bill_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_bill_amt,0),l_txn_currency_code);
1717: IF g1_debug_mode = 'Y' THEN
1718: PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event2: inv amt'||to_char(XD_bill_trans_bill_amt));
1719: END IF;
1720: /* The following amounts have been commented for MCB2, the above amounts are satisfying the same requirement */
1721: /* XD_bill_trans_rev_amt := (NVL(X_rev_amt, 0));
1722: XD_bill_trans_bill_amt := (NVL(X_bill_amt,0)); */
1722: XD_bill_trans_bill_amt := (NVL(X_bill_amt,0)); */
1723:
1724: -- Get defaults for other non-mandatory parameters
1725: XD_completion_date := nvl(X_completion_date,
1726: to_date(pa_billing.GlobVars.AccrueThruDate,'YYYY/MM/DD'));
1727:
1728:
1729: -- Added check to prevent event creation from other calling places like 'PRE' 'POST'
1730: -- 'DEL' etc.
1728:
1729: -- Added check to prevent event creation from other calling places like 'PRE' 'POST'
1730: -- 'DEL' etc.
1731: --
1732: IF (pa_billing.GlobVars.CallingPlace NOT IN ('REG','ADJ','POST-REG')) THEN
1733: raise invalid_calling_place;
1734: END IF;
1735:
1736:
1734: END IF;
1735:
1736:
1737: IF (X_organization_id IS NULL) THEN
1738: XD_organization_id := pa_billing_values.get_dflt_org(
1739: nvl(X_project_id,
1740: pa_billing.GlobVars.ProjectId),
1741: nvl(X_top_task_id,
1742: pa_billing.GlobVars.TaskId));
1736:
1737: IF (X_organization_id IS NULL) THEN
1738: XD_organization_id := pa_billing_values.get_dflt_org(
1739: nvl(X_project_id,
1740: pa_billing.GlobVars.ProjectId),
1741: nvl(X_top_task_id,
1742: pa_billing.GlobVars.TaskId));
1743: ELSE
1744: XD_organization_id := X_organization_id;
1738: XD_organization_id := pa_billing_values.get_dflt_org(
1739: nvl(X_project_id,
1740: pa_billing.GlobVars.ProjectId),
1741: nvl(X_top_task_id,
1742: pa_billing.GlobVars.TaskId));
1743: ELSE
1744: XD_organization_id := X_organization_id;
1745: END IF;
1746:
1745: END IF;
1746:
1747: IF (X_event_description IS NULL OR
1748: X_event_type IS NULL) THEN
1749: pa_billing_values.get_dflt_desc(pa_billing.GlobVars.BillingAssignmentId,
1750: XD_event_type, XD_event_description);
1751: XD_event_description := nvl(X_event_description,
1752: XD_event_description);
1753: XD_event_type := nvl(X_event_type, XD_event_type);
1762:
1763:
1764: -- Validate Id's
1765:
1766: IF (pa_billing_validate.valid_proj_task_extn(
1767: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1768: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
1769: pa_billing.GlobVars.BillingAssignmentId) AND
1770: pa_billing_validate.valid_organization(XD_organization_id)) THEN
1763:
1764: -- Validate Id's
1765:
1766: IF (pa_billing_validate.valid_proj_task_extn(
1767: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1768: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
1769: pa_billing.GlobVars.BillingAssignmentId) AND
1770: pa_billing_validate.valid_organization(XD_organization_id)) THEN
1771: NULL;
1764: -- Validate Id's
1765:
1766: IF (pa_billing_validate.valid_proj_task_extn(
1767: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1768: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
1769: pa_billing.GlobVars.BillingAssignmentId) AND
1770: pa_billing_validate.valid_organization(XD_organization_id)) THEN
1771: NULL;
1772: ELSE
1765:
1766: IF (pa_billing_validate.valid_proj_task_extn(
1767: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1768: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
1769: pa_billing.GlobVars.BillingAssignmentId) AND
1770: pa_billing_validate.valid_organization(XD_organization_id)) THEN
1771: NULL;
1772: ELSE
1773: RAISE INVALID_ID;
1766: IF (pa_billing_validate.valid_proj_task_extn(
1767: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1768: nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
1769: pa_billing.GlobVars.BillingAssignmentId) AND
1770: pa_billing_validate.valid_organization(XD_organization_id)) THEN
1771: NULL;
1772: ELSE
1773: RAISE INVALID_ID;
1774: END IF;
1776:
1777:
1778: -- Validate funding level
1779:
1780: IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
1781: IF (pa_billing_values.funding_level(nvl(X_project_id,
1782: pa_billing.GlobVars.ProjectId))
1783: <> 'PROJECT') THEN
1784: RAISE invalid_project_event;
1777:
1778: -- Validate funding level
1779:
1780: IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
1781: IF (pa_billing_values.funding_level(nvl(X_project_id,
1782: pa_billing.GlobVars.ProjectId))
1783: <> 'PROJECT') THEN
1784: RAISE invalid_project_event;
1785: END IF;
1778: -- Validate funding level
1779:
1780: IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
1781: IF (pa_billing_values.funding_level(nvl(X_project_id,
1782: pa_billing.GlobVars.ProjectId))
1783: <> 'PROJECT') THEN
1784: RAISE invalid_project_event;
1785: END IF;
1786: END IF;
1792: -- Validate amounts based on which process is calling.
1793: IF ((((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N')) AND
1794: (XD_bill_trans_bill_amt = 0)) OR
1795: ((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N') AND
1796: pa_billing.GlobVars.CallingProcess = 'Revenue') OR
1797: ((XD_bill_trans_bill_amt = 0) AND
1798: pa_billing.GlobVars.CallingProcess = 'Invoice')) THEN
1799: RAISE zero_amounts;
1800: END IF;
1794: (XD_bill_trans_bill_amt = 0)) OR
1795: ((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N') AND
1796: pa_billing.GlobVars.CallingProcess = 'Revenue') OR
1797: ((XD_bill_trans_bill_amt = 0) AND
1798: pa_billing.GlobVars.CallingProcess = 'Invoice')) THEN
1799: RAISE zero_amounts;
1800: END IF;
1801:
1802: /* Modified the below condition for Bug 9154825 */
1800: END IF;
1801:
1802: /* Modified the below condition for Bug 9154825 */
1803: -- Check original event num for the ADJ automatic events.
1804: IF (PA_BILLING.GetCallPlace = 'ADJ' AND
1805: (pa_billing.GlobVars.CallingProcess = 'Invoice' OR pa_billing.GlobVars.CallingProcess = 'Revenue') AND
1806: nvl(X_event_num_reversed, 0) = 0) THEN
1807: RAISE no_orig_event;
1808: END IF;
1801:
1802: /* Modified the below condition for Bug 9154825 */
1803: -- Check original event num for the ADJ automatic events.
1804: IF (PA_BILLING.GetCallPlace = 'ADJ' AND
1805: (pa_billing.GlobVars.CallingProcess = 'Invoice' OR pa_billing.GlobVars.CallingProcess = 'Revenue') AND
1806: nvl(X_event_num_reversed, 0) = 0) THEN
1807: RAISE no_orig_event;
1808: END IF;
1809:
1806: nvl(X_event_num_reversed, 0) = 0) THEN
1807: RAISE no_orig_event;
1808: END IF;
1809:
1810: event_num := pa_billing_seq.next_eventnum(
1811: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1812: nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
1813:
1814:
1807: RAISE no_orig_event;
1808: END IF;
1809:
1810: event_num := pa_billing_seq.next_eventnum(
1811: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1812: nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
1813:
1814:
1815: /* Adding validation of newly added columns in event table for project contract integration */
1808: END IF;
1809:
1810: event_num := pa_billing_seq.next_eventnum(
1811: nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1812: nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
1813:
1814:
1815: /* Adding validation of newly added columns in event table for project contract integration */
1816: -- Validating inventory_org_id and inventory_item_id
1815: /* Adding validation of newly added columns in event table for project contract integration */
1816: -- Validating inventory_org_id and inventory_item_id
1817:
1818: IF (x_inventory_org_id IS NOT NULL) THEN
1819: IF (pa_billing_validate.valid_organization(x_inventory_org_id)) THEN
1820: NULL;
1821: ELSE
1822: RAISE INVALID_INVENT_ID;
1823: END IF;
2052:
2053: l_event_id := pa_events_s.nextval;
2054:
2055:
2056: IF (pa_billing_validate.automatic_event(XD_event_type)) THEN
2057: insert into pa_events
2058: (PROJECT_ID, TASK_ID, ORGANIZATION_ID, EVENT_NUM, EVENT_TYPE, -- 1
2059: REVENUE_AMOUNT,BILL_AMOUNT,COMPLETION_DATE, REQUEST_ID, -- 2
2060: DESCRIPTION, BILL_HOLD_FLAG, REV_DIST_REJECTION_CODE, -- 3
2119: AUDIT_COST_PLAN_TYPE_ID, /* Added for Fin Plan impact */
2120: AUDIT_REV_PLAN_TYPE_ID /* Added for Fin Plan impact */
2121: )
2122: values
2123: (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
2124: XD_organization_id, event_num, XD_event_type, -- 1
2125: 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
2126: XD_event_description, 'N', NULL, -- 3
2127: 'N', program_application_id,program_id, -- 4
2121: )
2122: values
2123: (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
2124: XD_organization_id, event_num, XD_event_type, -- 1
2125: 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
2126: XD_event_description, 'N', NULL, -- 3
2127: 'N', program_application_id,program_id, -- 4
2128: sysdate, sysdate, nvl(last_updated_by,0), -- 5
2129: sysdate, nvl(created_by,0), nvl(last_update_login,0),
2131: X_attribute1, X_attribute2, X_attribute3, X_attribute4,
2132: X_attribute5, -- 7
2133: X_attribute6, X_attribute7, X_attribute8, X_attribute9,
2134: X_attribute10, -- 8
2135: pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
2136: pa_billing.GlobVars.CallingProcess, -- 9
2137: X_event_num_reversed, -- 10
2138: pa_currency.round_currency_amt(X_audit_amount1),
2139: pa_currency.round_currency_amt(X_audit_amount2),
2132: X_attribute5, -- 7
2133: X_attribute6, X_attribute7, X_attribute8, X_attribute9,
2134: X_attribute10, -- 8
2135: pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
2136: pa_billing.GlobVars.CallingProcess, -- 9
2137: X_event_num_reversed, -- 10
2138: pa_currency.round_currency_amt(X_audit_amount1),
2139: pa_currency.round_currency_amt(X_audit_amount2),
2140: pa_currency.round_currency_amt(X_audit_amount3),
2198:
2199:
2200:
2201: IF g1_debug_mode = 'Y' THEN
2202: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_event2: ');
2203: END IF;
2204: EXCEPTION
2205: WHEN mandatory_prm_missing THEN
2206: status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
2202: PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_event2: ');
2203: END IF;
2204: EXCEPTION
2205: WHEN mandatory_prm_missing THEN
2206: status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
2207: l_status := 1;
2208: RAISE;
2209: WHEN invalid_id THEN
2210: status := pa_billing_values.get_message('INVALID_ID');
2206: status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
2207: l_status := 1;
2208: RAISE;
2209: WHEN invalid_id THEN
2210: status := pa_billing_values.get_message('INVALID_ID');
2211: l_status := 2;
2212: RAISE;
2213: WHEN invalid_project_event THEN
2214: status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
2210: status := pa_billing_values.get_message('INVALID_ID');
2211: l_status := 2;
2212: RAISE;
2213: WHEN invalid_project_event THEN
2214: status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
2215: l_status := 3;
2216: RAISE;
2217: /*WHEN event_type_error THEN commenting this for bug 3492506
2218: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2214: status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
2215: l_status := 3;
2216: RAISE;
2217: /*WHEN event_type_error THEN commenting this for bug 3492506
2218: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2219: RAISE;*/
2220: WHEN null_event_type_error THEN
2221: status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
2222: l_status := 4;
2217: /*WHEN event_type_error THEN commenting this for bug 3492506
2218: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2219: RAISE;*/
2220: WHEN null_event_type_error THEN
2221: status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
2222: l_status := 4;
2223: RAISE;
2224: WHEN zero_amounts THEN
2225: status := pa_billing_values.get_message('ZERO_AMOUNTS');
2221: status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
2222: l_status := 4;
2223: RAISE;
2224: WHEN zero_amounts THEN
2225: status := pa_billing_values.get_message('ZERO_AMOUNTS');
2226: l_status := 5;
2227: RAISE;
2228: WHEN no_orig_event THEN
2229: status := pa_billing_values.get_message('NO_ORIG_EVENT');
2225: status := pa_billing_values.get_message('ZERO_AMOUNTS');
2226: l_status := 5;
2227: RAISE;
2228: WHEN no_orig_event THEN
2229: status := pa_billing_values.get_message('NO_ORIG_EVENT');
2230: l_status := 6;
2231: RAISE;
2232: WHEN invalid_calling_place THEN
2233: status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
2229: status := pa_billing_values.get_message('NO_ORIG_EVENT');
2230: l_status := 6;
2231: RAISE;
2232: WHEN invalid_calling_place THEN
2233: status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
2234: l_status := 7;
2235: RAISE;
2236: WHEN invalid_invent_id THEN
2237: status := pa_billing_values.get_message('INVALID_INVENT_ID');
2233: status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
2234: l_status := 7;
2235: RAISE;
2236: WHEN invalid_invent_id THEN
2237: status := pa_billing_values.get_message('INVALID_INVENT_ID');
2238: l_status := 8;
2239: RAISE;
2240: WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
2241: status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
2237: status := pa_billing_values.get_message('INVALID_INVENT_ID');
2238: l_status := 8;
2239: RAISE;
2240: WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
2241: status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
2242: l_status := 9;
2243: RAISE;
2244: WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
2245: status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
2241: status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
2242: l_status := 9;
2243: RAISE;
2244: WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
2245: status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
2246: l_status := 10;
2247: RAISE;
2248: WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
2249: status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
2245: status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
2246: l_status := 10;
2247: RAISE;
2248: WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
2249: status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
2250: l_status := 11;
2251: RAISE;
2252: WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
2253: status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
2249: status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
2250: l_status := 11;
2251: RAISE;
2252: WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
2253: status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
2254: l_status := 12;
2255: RAISE;
2256: WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
2257: status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
2253: status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
2254: l_status := 12;
2255: RAISE;
2256: WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
2257: status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
2258: l_status := 13;
2259: RAISE;
2260: WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
2261: status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
2257: status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
2258: l_status := 13;
2259: RAISE;
2260: WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
2261: status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
2262: l_status := 14;
2263: RAISE;
2264: WHEN l_invalid_currency THEN /* Added for MCB2 */
2265: status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
2261: status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
2262: l_status := 14;
2263: RAISE;
2264: WHEN l_invalid_currency THEN /* Added for MCB2 */
2265: status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
2266: l_status := 15;
2267: RAISE;
2268: WHEN event_type_error THEN /* adding this here for bug 3492506 */
2269: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2265: status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
2266: l_status := 15;
2267: RAISE;
2268: WHEN event_type_error THEN /* adding this here for bug 3492506 */
2269: status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2270: l_status := 16;
2271: RAISE;
2272: WHEN OTHERS THEN
2273: status := substr(SQLERRM,1,240);
2281: -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
2282: X_error_message := status;
2283: X_status := l_status;
2284:
2285: insert_message(X_inserting_procedure_name => 'pa_billing_pub.insert_event2',
2286: X_attribute1 => XD_bill_trans_rev_amt,
2287: X_attribute2 => XD_bill_trans_bill_amt,
2288: X_message => status,
2289: X_status => err_status,
2300:
2301:
2302: --ER 14089913
2303:
2304: END pa_billing_pub;