How to Call a Pkg/Procedure Executing an API From a Pkg/Procedure. The API name needs to be Dynamic and Has In and Out Parms

Question

We use APIs, baninst1.PP_DEDUCTION.p_update and baninst1.PP_DEDUCTION.p_create, to maintain our payroll tables of benefit/deduction data. Numerous packages utilize the APIs. We would like to create a package containing the API call that all the existing packages can use and remove the code that is repeated in each package. I tried EXECUTE IMMEDIATE for the purpose of having a dynamic API name. However, I have not been able to get the syntax correct. I’m hoping you will help me.

create or replace PACKAGE BODY "ORBIT"."MM_BENEFITS_COMMON" AS

PROCEDURE PAY_P_EMPLOYEE_BENEFIT_ACTION(pi_benefit_action IN VARCHAR2,
                                                                               pi_pidm                   IN pdrbded.pdrbded_pidm%TYPE,
                                                                               pi_status                  IN pdrdedn.pdrdedn_status%TYPE,
                                                                               pi_bdca_code          IN pdrbded.pdrbded_bdca_code%TYPE,
                                                                               pi_effective_date     IN pdrdedn.pdrdedn_effective_date%TYPE DEFAULT NULL,
                                                                               pi_user_id                IN pdrdedn.pdrdedn_user_id%TYPE DEFAULT NULL,
                                                                               pi_data_origin          IN pdrdedn.pdrdedn_data_origin%TYPE DEFAULT NULL,
                                                                               po_base_rowid_out  OUT gb_common.internal_record_id_type,                                                                                po_detail_rowid_out OUT gb_common.internal_record_id_type,
                                                                               pi_amount1              IN pdrdedn.pdrdedn_amount1%TYPE DEFAULT NULL,
                                                                               pi_opt_code1            IN pdrdedn.pdrdedn_opt_code1%TYPE DEFAULT NULL) IS

BEGIN
--Call the API for p_create or p_update.
baninst1.PP_DEDUCTION.pi_benefit_action(p_pidm                  => pi_pidm,
                                                                       p_status                 => pi_status,
                                                                       p_bdca_code          => pi_bdca_code,
                                                                       p_effective_date     => CASE
                                                                                                                 WHEN pi_benefit_action 'p_create' THEN
                                                                                                                       TRUNC(pi_begin_date)
                                                                                                                 ELSE
                                                                                                                       TRUNC(pi_effective_date)
                                                                                                             END,
                                                                      p_user_id                =>   pi_user_id,
                                                                      p_data_origin          =>   pi_data_origin,
                                                                      p_base_rowid_out   =>   po_base_rowid_out,
                                                                      p_detail_rowid_out  =>  po_detail_rowid_out,
                                                                      p_amount1              =>  pi_amount1,

                                                                      p_opt_code1            => CASE
                                                                                                                 WHEN LENGTH(pi_opt_code1) = 1 THEN
                                                                                                                        '0' || pi_opt_code1
                                                                                                                 ELSE pi_opt_code1
                                                                                                            END);

END PAY_P_EMPLOYEE_BENEFIT_ACTION;

END MM_BENEFITS_COMMON;

create or replace PACKAGE BODY "ORBIT"."MM_BENEFIT_TEST" AS

PROCEDURE PAY_P_MM_BENEFIT_TEST IS

lv_base_rowid_out    gb_common.internal_record_id_type;
lv_detail_rowid_out   gb_common.internal_record_id_type;

BEGIN
--Pass data to the common benefits package for the api call MM_BENEFITS_COMMON.PAY_P_EMPLOYEE_BENEFIT_ACTION('p_update', 9999999, 'A', 'VI1', '01-JAN-2022', 'MM_Test',     'MM_TEST', lv_base_rowid_out, lv_detail_rowid_out, 25.82, NULL);

END PAY_P_MM_BENEFIT_TEST;

END MM_BENEFIT_TEST;

Answer 1

I'm not sure what's bothering you, actually. You did post some code, but - I don't know what it represents.

Let's see what you said:

• "We use APIs, baninst1.PP_DEDUCTION.p_update and baninst1.PP_DEDUCTION.p_create, to maintain our payroll tables of benefit/deduction data."

  • OK

• "Numerous packages utilize the APIs."

  • it means that there are many packages and they call those p_update and p_create procedures; that's also OK

• "We would like to create a package containing the API call that all the existing packages can use and remove the code that is repeated in each package."

  • that would be a new package; you'd cut that piece of code from all of your packages and paste it into a new one.
    • OK, makes sense. Instead of all that code (in every package), you'd put call to newly created procedures (that reside in a newly created package)

• "I tried EXECUTE IMMEDIATE for the purpose of having a dynamic API name. However, I have not been able to get the syntax correct."

  • why dynamic SQL? There's nothing dynamic here. Instead of dozens of lines of code you currently have (that do something), you'd put one line - the one that calls that newly created procedure (and pass parameters)

---

From my point of view, there's nothing unusual in what you want to do and I can't imagine what problems you could have in doing it; it's pretty much straightforward.