Oracle App R12 submit a concurrent request from a FORM module through button and view output

Block in the button form:

declare 

url    varchar2(2000);

 L_TXN_ID_D NUMBER;

   begin

    L_TXN_ID_D:= :FUJ_IMM_TXN_D.TXN_ID_D;

 url:= xximm_submit_report.xx_hr_report_retrun(

p_template =>'XX_IMM_WK' /*XML report template code*/

,p_application => 'XXCUST'

,p_program => 'XX_IMM_WK ' --Concurrent Program Code

,p_arg1 => L_TXN_ID_D  --Parameters

);

 

web.show_document(url);  

   exception when others then 

    message(sqlerrm);

   end;

CREATE OR REPLACE PACKAGE APPS.xximm_submit_report

AS

   PROCEDURE xx_get_mime_type (

      ftype   IN       VARCHAR2,

      mtype   IN OUT   VARCHAR2,

      wopen   IN OUT   BOOLEAN

   );

   FUNCTION xx_browser_show_retrun (

      fname    VARCHAR2,

      node     VARCHAR2,

      mtype    VARCHAR2,

      req_id   NUMBER DEFAULT NULL

   )

      RETURN VARCHAR2;

     FUNCTION xx_hr_report_retrun (

      p_template         IN   VARCHAR2 DEFAULT NULL,

      p_application      IN   VARCHAR2 DEFAULT NULL,

      p_program          IN   VARCHAR2 DEFAULT NULL,

      p_description      IN   VARCHAR2 DEFAULT NULL,

      p_arg1             IN   VARCHAR2 DEFAULT NULL,

      p_arg2             IN   VARCHAR2 DEFAULT NULL,

      p_arg3             IN   VARCHAR2 DEFAULT NULL,

      p_arg4             IN   VARCHAR2 DEFAULT NULL,

      p_arg5             IN   VARCHAR2 DEFAULT NULL,

      p_arg6             IN   VARCHAR2 DEFAULT NULL,

      p_arg7             IN   VARCHAR2 DEFAULT NULL

   )

      RETURN VARCHAR2;

END xximm_submit_report;

/

 

CREATE OR REPLACE PACKAGE BODY APPS.xximm_submit_report

AS

   PROCEDURE xx_get_mime_type (

      ftype   IN       VARCHAR2,

      mtype   IN OUT   VARCHAR2,

      wopen   IN OUT   BOOLEAN

   )

   IS

      profile_defined   BOOLEAN       := FALSE;

      errcode           NUMBER;

      g_size            NUMBER;

      initial_mtype     VARCHAR2 (80);    -- initial value for viewer poplist

   BEGIN

      wopen := FALSE;

      -- check profile options for each file format

      IF (ftype = 'TEXT')

      THEN

         IF (fnd_profile.defined ('FS_MIME_TEXT'))

         THEN

            fnd_profile.get ('FS_MIME_TEXT', mtype);

            profile_defined := TRUE;

         END IF;

      ELSIF (ftype = 'HTML')

      THEN

         IF (fnd_profile.defined ('FS_MIME_HTML'))

         THEN

            fnd_profile.get ('FS_MIME_HTML', mtype);

            profile_defined := TRUE;

         END IF;

      ELSIF (ftype = 'PDF')

      THEN

         IF (fnd_profile.defined ('FS_MIME_PDF'))

         THEN

            fnd_profile.get ('FS_MIME_PDF', mtype);

            profile_defined := TRUE;

         END IF;

      ELSIF (ftype = 'PS')

      THEN

         IF (fnd_profile.defined ('FS_MIME_PS'))

         THEN

            fnd_profile.get ('FS_MIME_PS', mtype);

            profile_defined := TRUE;

         END IF;

      ELSIF (ftype = 'PCL')

      THEN

         IF (fnd_profile.defined ('FS_MIME_PCL'))

         THEN

            fnd_profile.get ('FS_MIME_PCL', mtype);

            profile_defined := TRUE;

         END IF;

      ELSIF (ftype = 'XML')

      THEN

         IF (fnd_profile.defined ('FS_MIME_XML'))

         THEN

            fnd_profile.get ('FS_MIME_XML', mtype);

            profile_defined := TRUE;

         END IF;

      ELSE

         mtype := 'text/plain';

         profile_defined := TRUE;

      END IF;

      IF NOT wopen

      THEN

         DBMS_OUTPUT.put_line ('Wopen False');

      ELSE

         DBMS_OUTPUT.put_line ('Wopen true');

      END IF;

      -- if profile option does not exists then look for fnd_mime_types_vl

      -- view for mime type.

      IF (NOT profile_defined)

      THEN

         FOR c IN (SELECT mime_type

                     FROM fnd_mime_types_vl

                    WHERE file_format_code = ftype AND ROWNUM = 1)

         LOOP

            mtype := c.mime_type;

            EXIT;

         END LOOP;

      END IF;                                           -- not profile_defined

   END;

   FUNCTION xx_browser_show_retrun (

      fname    VARCHAR2,

      node     VARCHAR2,

      mtype    VARCHAR2,

      req_id   NUMBER DEFAULT NULL

   )

      RETURN VARCHAR2

   IS

      base             VARCHAR2 (255);

      url              VARCHAR2 (255);

      ID               VARCHAR2 (32);

      gwyuid           VARCHAR2 (32);

      two_task         VARCHAR2 (64);

      fs_enabled       VARCHAR2 (2);

      pos              NUMBER;

      svc              VARCHAR2 (240);

      x_mode           VARCHAR2 (30)  := 'BINARY';

      oftype           VARCHAR2 (4);

      published_req    BOOLEAN        := FALSE;

      pub_reqid        VARCHAR2 (32);

      int_request_id   NUMBER;

      base_type        VARCHAR2 (50);

      PRAGMA AUTONOMOUS_TRANSACTION;

   BEGIN

      int_request_id := req_id;

      DBMS_OUTPUT.put_line ('node ===>' || node);

      DBMS_OUTPUT.put_line ('mtype ===>' || mtype);

      DBMS_OUTPUT.put_line ('req_id ===>' || req_id);

      fnd_profile.get ('TWO_TASK', two_task);

      fnd_profile.get ('GWYUID', gwyuid);

      IF (fnd_profile.defined ('APPS_CGI_AGENT'))

      THEN

         fnd_profile.get ('APPS_CGI_AGENT', base);

      END IF;

      IF (base IS NULL)

      THEN

         fnd_profile.get ('APPS_WEB_AGENT', base);

         base_type := 'WEB';

      END IF;

      IF (fnd_profile.defined ('FS_SVC_PREFIX'))

      THEN

         fnd_profile.get ('FS_SVC_PREFIX', svc);

         IF (svc IS NOT NULL)

         THEN

            svc := SUBSTR (svc || node, 1, 255);

         ELSE

            svc := 'FNDFS_' || node;

         END IF;

      ELSE

         svc := 'FNDFS_' || node;

      END IF;

      IF (req_id IS NOT NULL)

      THEN

         BEGIN

            SELECT file_type

              INTO oftype

              FROM fnd_conc_req_outputs

             WHERE concurrent_request_id = req_id AND ROWNUM = 1;

            IF (oftype IN ('PDF', 'PS', 'PCL', 'EXCEL'))

            THEN

               x_mode := 'BINARY';

            END IF;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               BEGIN

                  SELECT NVL (output_file_type, 'TEXT')

                    INTO oftype

                    FROM fnd_concurrent_requests

                   WHERE request_id = req_id;

                  IF (oftype IN ('PDF', 'PS', 'PCL','XML'))

                  THEN

                     x_mode := 'BINARY';

                  END IF;

               EXCEPTION

                  WHEN NO_DATA_FOUND

                  THEN

                     x_mode := 'TEXT';

               END;

         END;

      END IF;

      DBMS_OUTPUT.put_line ('X_mode:' || x_mode);

      IF published_req = TRUE

      THEN

         int_request_id := pub_reqid;

         DBMS_OUTPUT.put_line ('published_req ===>true');

      ELSE

         DBMS_OUTPUT.put_line ('published_req ===>false');

      END IF;

      ID :=

         fnd_webfile.create_id (fname,

                                svc,

                                10,

                                mtype,

                                NVL (int_request_id, req_id),

                                x_mode,

                                'Y'

                               );

      DBMS_OUTPUT.put_line ('fname ===>' || fname);

      DBMS_OUTPUT.put_line ('svc ==>' || svc);

      DBMS_OUTPUT.put_line ('mtype' || mtype);

      DBMS_OUTPUT.put_line ('ID ===>' || ID);

      DBMS_OUTPUT.put_line ('pub_reqid ==>' || pub_reqid);

      DBMS_OUTPUT.put_line ('ID after publish' || ID);

      base := LTRIM (RTRIM (base));

      IF (base_type = 'WEB')

      THEN

         -- Strip any file path from the base URL by truncating at the

         -- third '/'.

         -- This leaves us with something like 'http://ap363sun:8000'.

         pos := INSTR (base, '/', 1, 3);

         IF (pos > 0)

         THEN

            base := SUBSTR (base, 1, pos - 1);

         END IF;

         -- 2638328 - security violation - removing login information from URL

         url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || ID;

      ELSIF (base_type = 'CGI')

      THEN

         IF (SUBSTR (base, LENGTH (base)) <> '/')

         THEN

            base := base || '/';

         END IF;

         url := base || 'FNDWRR.exe?' || 'temp_id=' || ID;

      END IF;

      DBMS_OUTPUT.put_line ('Before : ' || url);

      --  FND_WEBFILE.GET_URL(FND_WEBFILE.request_out,ID,gwyuid,two_task,100);

      url :=

         fnd_webfile.get_url (file_type        => fnd_webfile.request_out,

                              ID               => req_id,

                              gwyuid           => gwyuid,

                              two_task         => two_task,

                              expire_time      => 100   -- minutes, security!.

                             );

      DBMS_OUTPUT.put_line ('after : ' || url);

/*    pos := INSTR(base, '/', 1, 3);

    IF (pos > 0) THEN

      base := SUBSTR(base, 1, pos - 1);

    END IF;

    url := base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || ID ;--|| '&' || 'login=' || gwyuid || '@' || two_task;

*/

      /*

          htp.htmlOpen;

          htp.headOpen;

          htp.title('Get request output');

          htp.headClose;

          htp.bodyOpen;

          htp.p('<A HREF='||url||'>Click to get request output</a>');

          htp.bodyclose;

          htp.htmlclose;

      */

      DBMS_OUTPUT.put_line (url);

      RETURN url;

   END;

FUNCTION xx_hr_report_retrun (

      p_template         IN   VARCHAR2 DEFAULT NULL,

      p_application      IN   VARCHAR2 DEFAULT NULL,

      p_program          IN   VARCHAR2 DEFAULT NULL,

      p_description      IN   VARCHAR2 DEFAULT NULL,

      p_arg1             IN   VARCHAR2 DEFAULT NULL,

      p_arg2             IN   VARCHAR2 DEFAULT NULL,

      p_arg3             IN   VARCHAR2 DEFAULT NULL,

      p_arg4             IN   VARCHAR2 DEFAULT NULL,

      p_arg5             IN   VARCHAR2 DEFAULT NULL,

      p_arg6             IN   VARCHAR2 DEFAULT NULL,

      p_arg7             IN   VARCHAR2 DEFAULT NULL

   )

      RETURN VARCHAR2

   IS

      l_req_id              NUMBER;

      l_user_id             NUMBER              := fnd_profile.VALUE ('USER');

      l_resp_id             NUMBER                                   := 52168;--53237;

      l_resp_appl_id        NUMBER                                     := 800;

      l_url                 VARCHAR2 (3000);

      ftype                 VARCHAR2 (30)                            := 'XML';

      mtype                 VARCHAR2 (80);

      wopen                 BOOLEAN                                  := FALSE;

      filename              fnd_concurrent_requests.outfile_name%TYPE;

      node                  fnd_concurrent_requests.outfile_node_name%TYPE;

      l_request_completed   BOOLEAN                                  := FALSE;

      l_req_phase           VARCHAR2 (20);

      l_req_status          VARCHAR2 (1000);

      l_req_dev_phase       VARCHAR2 (1000);

      l_req_dev_status      VARCHAR2 (1000);

      l_req_message         VARCHAR2 (1000);

      b_success             BOOLEAN;

      v_set_layout_option   BOOLEAN;

      v_error_message       VARCHAR (2000);

      v_count               NUMBER;

      v_template_type       VARCHAR2 (200);

      v_cycle_name          VARCHAR2 (200);

      v_traning_v           VARCHAR2 (200);

      v_comb                VARCHAR2 (200);

   BEGIN

   -- v_template_type := 'FUJ_HR_HOTEL_WKPERMIT';

fnd_global.apps_initialize(2605,52168, 800);

      v_set_layout_option :=

         fnd_request.add_layout (template_appl_name      => p_application,

                                      template_code           => p_template,

                                      --p_program,

                                      template_language       => 'en',

                                      template_territory      => '',

                                      output_format           => 'PDF'

                                     );

      IF (NOT v_set_layout_option)

      THEN

         fnd_file.put_line (fnd_file.LOG,

                                 'Unable to apply template'

                                );

         fnd_file.put_line (fnd_file.LOG, v_error_message);

      ELSE

           l_req_id :=

            fnd_request.submit_request (application      => p_application,

                                        program          => p_program,

                                        description      => null,--p_description,

                                        start_time       => NULL,

                                        argument1        => p_arg1,

                                        argument2        => p_arg2,

                                        argument3        => p_arg3,

                                        argument4        => p_arg4,

                                        argument5        => p_arg5,

                                        argument6        => p_arg6,

                                        argument7        => p_arg7

                                       );

         DBMS_OUTPUT.put_line ('l_req_id ===> ' || l_req_id);

         COMMIT;

      END IF;

      l_request_completed :=

         fnd_concurrent.wait_for_request (request_id      => l_req_id,

                                          INTERVAL        => 1,

                                          phase           => l_req_phase,

                                          status          => l_req_status,

                                          dev_phase       => l_req_dev_phase,

                                          dev_status      => l_req_dev_status,

                                          MESSAGE         => l_req_message

                                         );

      COMMIT;

      FOR c IN (SELECT NVL (output_file_type, 'PDF') ftype,

                       outfile_name filename, outfile_node_name node,

                       ofile_size

                  FROM fnd_concurrent_requests

                 WHERE request_id = l_req_id)

      LOOP

         ftype := c.ftype;

         filename := c.filename;

         node := c.node;

         EXIT;

      END LOOP;

      xx_get_mime_type (ftype => ftype, mtype => mtype, wopen => wopen);

      IF NOT wopen

      THEN

         l_url := xx_browser_show_retrun (filename, node, mtype, l_req_id);

      END IF;

      RETURN l_url;

   END;

END xximm_submit_report;

/

Oracle SQL Query for Customer Details

  SELECT DISTINCT hp.party_name           "CUSTOMER_NAME",

                      hca.account_number,

                      hca.status,

                      hca.cust_account_id     customer_id,

                      HOU.NAME                "OPERATING_UNIT"

        FROM apps.hz_parties            hp,

             apps.hz_party_sites        hps,

             apps.hz_locations          hl,

             apps.hz_cust_accounts      hca,

             apps.hz_cust_acct_sites_all hcas,

             apps.hz_cust_site_uses_all hcsu,

             --apps.hz_customer_profiles hcp,

             --apps.hz_cust_profile_classes hcpc,

             --apps.ar_payment_schedules_all ps,

             apps.hr_operating_units    hou

       WHERE     hp.party_id = hca.party_id(+)

             --AND hp.party_id             = hcp.party_id

             AND hp.party_id = hps.party_id

             AND hps.party_site_id = hcas.party_site_id

             AND hps.location_id = hl.location_id

             AND hca.cust_account_id = hcas.cust_account_id

             AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

             --AND hca.cust_account_id     = hcp.cust_account_id

             --AND hca.cust_account_id     = ps.customer_id

             --AND hcp.profile_class_id    = hcpc.profile_class_id

             --AND ps.customer_site_use_id =hcsu.site_use_id

             AND hcsu.org_id = hou.organization_id

             AND hcsu.ORG_ID = 2352

Oracle Apps R12 Assignment Mode Status Function

 

FUNCTION GET_MODE_ASSGINMENT_F(P_ASSIGNMENT_ID NUMBER, P_DATE DATE)

RETURN VARCHAR2 AS

   lb_correction                  BOOLEAN;

   lb_update                      BOOLEAN;

   lb_update_override             BOOLEAN;

   lb_update_change_insert        BOOLEAN;

   lc_dt_ud_mode varchar2(200); 

   L_ERROR varchar2(2000); 

   BEGIN

dt_api.find_dt_upd_modes

   (    p_effective_date                  => P_DATE,

        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',

        p_base_key_column            => 'ASSIGNMENT_ID',

        p_base_key_value                => P_ASSIGNMENT_ID,

         -- Output data elements

         -- --------------------------------

         p_correction                   => lb_correction,

         p_update                       => lb_update,

         p_update_override              => lb_update_override,

         p_update_change_insert    => lb_update_change_insert  ); 

 

   IF ( lb_correction = TRUE )

   THEN lc_dt_ud_mode := 'CORRECTION';

   end if;

   IF ( lb_update = TRUE )

   THEN lc_dt_ud_mode := 'UPDATE';

   END IF; 

   IF ( lb_update_override = TRUE  )

   THEN lc_dt_ud_mode := 'UPDATE_OVERRIDE';

   end if;

   IF (lb_update_change_insert = TRUE)

   THEN lc_dt_ud_mode := 'UPDATE_CHANGE_INSERT';

   end if;

  

   RETURN lc_dt_ud_mode;

   EXCEPTION WHEN OTHERS THEN 

   L_ERROR := SQLERRM;

END;

Oracle Apps R12 Object Version Number Function

 FUNCTION GET_OVN_F (P_ASSIGNMENT_ID NUMBER, P_DATE DATE) RETURN NUMBER IS

L_RETURN NUMBER;

BEGIN

SELECT OBJECT_VERSION_NUMBER 

INTO L_RETURN

FROM PER_ALL_ASSIGNMENTS_F PAAF

WHERE ASSIGNMENT_ID = P_ASSIGNMENT_ID

AND P_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

AND PRIMARY_FLAG = 'Y'

;

RETURN L_RETURN;

EXCEPTION WHEN OTHERS THEN RETURN 0;

END;

Use as Below in any package:

ln_object_number := 0;

  ln_object_number :=GET_OVN_F(:ASSIGNMENT_ID,:RES_DATE);

Oracle Apps R12 for Create Element Entry api

 PROCEDURE FUJ_CRT_ELEMENT_ENTRY

(

L_ASSIGNMENT_ID NUMBER,

L_PAYROLL_ID NUMBER,

L_EFF_DATE DATE,

L_AMOUNT NUMBER,

L_ELEMENT_NAME VARCHAR2,

L_INPUT_NAME  VARCHAR2

) AS

--L_ASSIGNMENT_ID NUMBER := 45570;

--L_PAYROLL_ID NUMBER := 60;

--L_ELEMENT_NAME VARCHAR2(200) := 'XX123 Allowance';

--L_INPUT_NAME  VARCHAR2(200) := 'Override Amount';

--L_EFF_DATE DATE := TO_DATE('20-DEC-2022');

--L_AMOUNT NUMBER := 200;

--local variables

l_input_value_id NUMBER;

l_link_id NUMBER;

 l_effective_end_date      DATE := NULL;

 l_effective_start_date    DATE := NULL;

   l_object_version_number   NUMBER := NULL;

   l_element_entry_id        NUMBER := NULL;

   l_create_warning          BOOLEAN;

 lc_dt_ud_mode           VARCHAR2(100)    := NULL;

 L_OVN  NUMBER;

 L_ELEMENT_EXISTS VARCHAR2(200);

 V_ERROR_MSG varchar2(2000);

begin

          

                   SELECT XXI.input_value_id, element_link_id,

                             INTO l_input_value_id, l_link_id

                                 FROM 

                                PAY_INPUT_VALUES_F XXI, 

                                PAY_ELEMENT_TYPES_F XXT,

                                PAY_ELEMENT_LINKS_F XXL

                                WHERE XXT.ELEMENT_TYPE_ID = XXI.ELEMENT_TYPE_ID

                                AND XXT.ELEMENT_TYPE_ID = XXL.ELEMENT_TYPE_ID

                                AND XXL.PAYROLL_ID = L_PAYROLL_ID

                                AND XXT.ELEMENT_NAME = L_ELEMENT_NAME--in ('XX123 Allowance')

                                and XXI.NAME = L_INPUT_NAME

                                AND ROWNUM =1

                                ;

                               

                                                                     

                         pay_element_entry_api.create_element_entry (

                            p_validate                => FALSE,

                            p_effective_date          => L_EFF_DATE,

                            p_business_group_id       => 81, 

                            p_assignment_id           => L_ASSIGNMENT_ID,

                            p_element_link_id         => l_link_id,

                            p_entry_type              => 'E',

                            p_input_value_id1                 => l_input_value_id

                            ,p_entry_value1                  => L_AMOUNT,

                            p_effective_start_date    => l_effective_start_date,

                            p_effective_end_date      => l_effective_end_date,

                            p_element_entry_id        => l_element_entry_id,

                            p_object_version_number   => l_object_version_number,

                            p_create_warning          => l_create_warning

                         );

                          commit;

 

 EXCEPTION WHEN OTHERS THEN

                         DBMS_OUTPUT.PUT_LINE(SQLERRM);

                         V_ERROR_MSG := sqlerrm;

                         

END;

Apps R12 Delete Absence API

 DECLARE

   CURSOR get_absence

   IS

      SELECT absence_attendance_id, object_version_number

        FROM per_absence_attendances

       WHERE PERSON_ID IN (SELECT PERSON_ID FROM PER_ALL_ASSIGNMENTS_F WHERE PAYROLL_ID =1002);

BEGIN

   FOR fetch_absence IN get_absence

   LOOP

      BEGIN

         hr_person_absence_api.

          delete_person_absence (

            p_validate                => FALSE,

            p_absence_attendance_id   => fetch_absence.absence_attendance_id,

            p_object_version_number   => fetch_absence.object_version_number);

         COMMIT;

         DBMS_OUTPUT.

          put_line (

            fetch_absence.absence_attendance_id

            || ' has been deleted Successfully !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

             put_line (

                  'Inner Exception: '

               || fetch_absence.absence_attendance_id

               || ' - - '

               || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

end;

Apps R12 HR Delete SIT API

 DECLARE

   CURSOR c1

   IS

     

 SELECT paa.person_analysis_id, paa.object_version_number,PER.EMPLOYEE_NUMBER

        FROM per_person_analyses paa,

             per_analysis_criteria pac,

             fnd_id_flex_structures_vl fifv,

             per_all_people_f per

       WHERE     paa.analysis_criteria_id = pac.analysis_criteria_id

             AND fifv.id_flex_num = paa.id_flex_num

             AND UPPER (fifv.id_flex_structure_code) = UPPER('XX_Leave_Details')

             AND fifv.id_flex_code = 'PEA'

             AND paa.date_from BETWEEN per.effective_start_date

                                   AND per.effective_end_date

             AND paa.person_id = per.person_id

             AND PER.PERSON_ID IN (SELECT PERSON_ID FROM PER_ALL_ASSIGNMENTS_F WHERE PAYROLL_ID =1002)

             ORDER BY 3 DESC

             ;

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         hr_sit_api.

          delete_sit (

            p_validate                    => FALSE,

            p_person_analysis_id          => i.person_analysis_id,

            p_pea_object_version_number   => i.object_version_number);

         COMMIT;

         DBMS_OUTPUT.

          put_line (

            i.person_analysis_id || ' has been deleted successfully !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

             put_line (

               'Failure: ' || i.person_analysis_id || ' - ' || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception:  ' || SQLERRM);

END;

Oracle Apps R12 Create AR Invoice to exclude TAX engine

API:  AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE

Important Line: l_trx_lines_tbl(1).taxable_flag := 'N';

How To Create An Invoice With Manual Tax Lines Using Invoice API 

(Doc ID 821259.1)

Customize PO Approval workflow to add NonRecoverable Tax in the Approver Notification Message

Requirement: 

User Enter the PO All Details and submit for Approval.

Actions --> Manage --> can view the NonRecoverable Tax, Recoverable Tax.

PO Approval workflow to add NonRecoverable Tax in the Approver Notification Message

1. New Attribute 

XXRECOVERABLE_TAX

2. Open Process

3. Under Message add your new attribute value

Recoverable Tax : &XXRECOVERABLE_TAX

4. New Function (XX_PONOTIF_TO_SUPPLIER)

Function Name; XX_PONOTIF_TO_SUPPLIER 🡪 this is the procedure in the database available at the end.

5. Attach this function to the process.

/* Formatted on 10/27/2022 2:18:31 PM (QP5 v5.336) */

CREATE OR REPLACE PROCEDURE XX_PONOTIF_TO_SUPPLIER (

    p_item_type   IN            VARCHAR2,

    p_item_key    IN            VARCHAR2,

    p_actid       IN            NUMBER,

    p_funmode     IN            VARCHAR2,

    p_result         OUT NOCOPY VARCHAR2)

IS

    L_RECOVER_TAX        NUMBER;

    L_RECOVER_TAX_CHAR   VARCHAR2 (200);

BEGIN

    BEGIN

        SELECT SUM (POD.RECOVERABLE_TAX)

          INTO L_RECOVER_TAX

          /*nvl(sum( round (nvl(POD.RECOVERABLE_TAX,0) *

                                    decode(quantity_ordered,

                                           NULL,

                                           (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / nvl(POD.amount_ordered, 1),

                                           (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / nvl(POD.quantity_ordered, 1)

                                          ) / 5

                                    ) * 5

                           ),0)*/

          FROM po_distributions_all POD, PO_HEADERS_ALL POA, apps.wf_items wi

         WHERE     POD.po_header_id = POA.PO_HEADER_ID --POHdr_rec.po_header_id

               AND NVL (POD.distribution_type, 'STANDARD') NOT IN

                       ('PREPAYMENT')                              -- 11876122

               AND POD.line_location_id IS NOT NULL

               AND wi.item_type = 'POAPPRV'

               AND wi.item_key = p_item_key

               AND wi.user_key = POA.segment1;

        L_RECOVER_TAX_CHAR := TO_CHAR (NVL (L_RECOVER_TAX, 0));

        wf_engine.setitemattrtext (itemtype   => p_item_type,

                                   itemkey    => p_item_key,

                                   aname      => 'XXRECOVERABLE_TAX',

                                   avalue     => L_RECOVER_TAX_CHAR);

    EXCEPTION

        WHEN OTHERS

        THEN

            AP_WEB_UTILITIES_PKG.logProcedure ('ERROR IN XXRECOVERABLE_TAX',

                                               'sqlerrm' || SQLERRM);

            wf_engine.setitemattrtext (itemtype   => p_item_type,

                                       itemkey    => p_item_key,

                                       aname      => 'XXRECOVERABLE_TAX',

                                       avalue     => '0');

    END;

    p_result := 'Y';

EXCEPTION

    WHEN OTHERS

    THEN

        p_result := 'N';

END;

Output:

Submit PO for Approval and check the Approver Notification. 

Item Creation with catalog, templates apps R12

--Stock Items

CREATE TABLE XXFIA_ITEM_INTERFACE

(ITEM_CODE VARCHAR2(30),

DESCRIPTION VARCHAR2(240),

SEGMENT1  VARCHAR2(240),

SEGMENT2  VARCHAR2(240),

CATEGORY_ID NUMBER,

UOM  VARCHAR2(240),

EXPENSE_ACCOUNT_SEG  VARCHAR2(240),

EXPENSE_ACCOUNT NUMBER,

SOURCE_NUMBER  VARCHAR2(240),

PROCESS_FLAG VARCHAR2(2),

ERROR_MSG  VARCHAR2(240));

--Non Stock Items

CREATE TABLE XXFIA_ITEM_INTERFACE_NS

(ITEM_CODE VARCHAR2(30),

DESCRIPTION VARCHAR2(240),

SEGMENT1  VARCHAR2(240),

SEGMENT2  VARCHAR2(240),

CATEGORY_ID NUMBER,

UOM  VARCHAR2(240),

EXPENSE_ACCOUNT_SEG  VARCHAR2(240),

EXPENSE_ACCOUNT NUMBER,

SOURCE_NUMBER  VARCHAR2(240),

PROCESS_FLAG VARCHAR2(2),

ERROR_MSG  VARCHAR2(240))

;

1. Upload using toad or any other tool.

2. Check if all the UOM exists in the masters.

select distinct trim(UOM),1

 from XXFIA_ITEM_INTERFACE

 WHERE TRIM(UOM)NOT IN (SELECT uom_code

FROM 

MTL_UNITS_OF_MEASURE MTL) 

UNION

select distinct trim(UOM),2

 from XXFIA_ITEM_INTERFACE_NS

 WHERE TRIM(UOM) NOT IN (SELECT trim(uom_code)

FROM 

MTL_UNITS_OF_MEASURE MTL)

ORDER BY 1

 ;

--truncate table XXFIA_ITEM_INTERFACE;

3. get the category_id

select category_id,segment1,SEGMENT2, description,category_concat_segs, structure_name

from mtl_categories_v

--where segment1 = '%ELEC%'

order by category_id,structure_id;

SELECT * FROM XXFIA_ITEM_INTERFACE

WHERE CATEGORY_ID IS NULL

;  

4. update category_id

DECLARE

CURSOR C1 IS 

SELECT CATEGORY_ID,SEGMENT1, SEGMENT2 FROM mtl_categories_v;

BEGIN

FOR I IN C1 LOOP

UPDATE XXFIA_ITEM_INTERFACE 

SET CATEGORY_ID = I.CATEGORY_ID

WHERE UPPER(I.SEGMENT1) = TRIM(UPPER(SEGMENT1)) 

AND UPPER(I.SEGMENT2) = TRIM(UPPER(SEGMENT2))

AND CATEGORY_ID IS NULL

;

COMMIT;

END LOOP;

END;

SELECT DISTINCT TRIM(SEGMENT1), TRIM(SEGMENT2) FROM XXFIA_ITEM_INTERFACE

WHERE CATEGORY_ID IS NULL

5. get the Master Organization, Other Oranizations.

--2398 MASTER

--2352 CHILD

6. Check if the accounts combination exists in GL  or not.

SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,EXPENSE_ACCOUNT_SEG

,3083 --TEMPLATE_ID 

--,3391161

, fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT

FROM XXFIA_ITEM_INTERFACE 

where

fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000') = '0';

SELECT * FROM GL_CODE_COMBINATIONS_V

WHERE CODE_COMBINATION_ID = 3391161;

7. Get the template Id and insert into the interface tables.

--STOCK TEMPLATE_ID  3083

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

,EXPENSE_ACCOUNT

)

SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3083 --TEMPLATE_ID 

--,3391161

, fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT

FROM XXFIA_ITEM_INTERFACE 

WHERE ITEM_CODE = ('67000450');

8. Run the concurrent Program from Inventory Resp

Import Items with Organization All, and Proces Flag as 1 and other as yes.

9.

For Categories.

insert into MTL_ITEM_CATEGORIES_INTERFACE

(

INVENTORY_ITEM_ID,

item_number,

category_set_id,  -- SAME for all --select category_set_id,category_set_name,description,structure_name, structure_id from mtl_category_sets_v where category_set_name LIKE 'FIA%';

category_id, --updated using above script

process_flag, organization_id, set_process_id, transaction_type)

--values('TESTCATG2',1100000041,2127,1,2398,1,'CREATE');

SELECT 

(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = TRIM(ITEM_CODE) and organization_id =2398 ),

TRIM(ITEM_CODE)

,1100000041 --static get once for all

,category_id

,1

,2398 --MASTER_ORG_ID

,1,'CREATE' FROM XXFIA_ITEM_INTERFACE 

WHERE TRIM(ITEM_CODE) = ('67000450'); 

10. Again Run the concurrent Program from Inventory Resp

Import Items with Organization All, and Proces Flag as 1 and other as yes.

11. Before adding to other organizations

we need to run the datafix 

---RUN DATAFIX

12.

--FOR CHILD ORG

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

,EXPENSE_ACCOUNT

)

SELECT 1,1,'CREATE',2352 --CHILD_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3083 --TEMPLATE_ID 

--,3391161

, fnd_flex_ext.get_ccid

            (application_short_name => 'SQLGL'

            ,key_flex_code          => 'GL#'

            ,structure_number       => 50522

            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')

            ,concatenated_segments  => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT

FROM XXFIA_ITEM_INTERFACE WHERE TRIM(ITEM_CODE) NOT IN ( '67000450')

;

COMMIT;

13.

Again Run the concurrent Program from Inventory Resp

Import Items with Organization All, and Proces Flag as 1 and other as yes.

14. do the same for Non Stock Items also.

--------------Non Stock--------------

SELECT * FROM XXFIA_ITEM_INTERFACE_NS;

TEMPLATE_ID:3084 --NON STOCK

DELETE FROM mtl_system_items_interface;

1.

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

--,EXPENSE_ACCOUNT

,MARKET_PRICE

)

SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3084 --TEMPLATE_ID 

,TRUNC(EXPENSE_ACCOUNT_SEG,2)

FROM XXFIA_ITEM_INTERFACE_NS 

WHERE ITEM_CODE <>'NG000016'-- <> 'NGE00001'

AND CATEGORY_ID IS NOT NULL

AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)

--AND TRIM(ITEM_CODE) = 'NB000143'

;

2. concurrent Program.

3.

insert into MTL_ITEM_CATEGORIES_INTERFACE

(

INVENTORY_ITEM_ID,

item_number,

category_set_id,  -- SAME for all --select category_set_id,category_set_name,description,structure_name, structure_id from mtl_category_sets_v where category_set_name LIKE 'FIA%';

category_id, --updated using above script

process_flag, organization_id, set_process_id, transaction_type)

----values('TESTCATG2',1100000041,2127,1,2398,1,'CREATE');

SELECT 

(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = TRIM(ITEM_CODE) AND ORGANIZATION_ID = 2398),

TRIM(ITEM_CODE)

,1100000041 --static get once for all

,category_id

,1

,2398 --MASTER_ORG_ID

,1,'CREATE' FROM XXFIA_ITEM_INTERFACE_NS 

WHERE ITEM_CODE <> 'NG000016'--<> 'NGE00001'

AND CATEGORY_ID IS NOT NULL

--AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)

;

4. concurrent Program.

5.

--FOR CHILD ORG

insert into mtl_system_items_interface

(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,

description

,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;

--,EXPENSE_ACCOUNT

)

SELECT 1,1,'CREATE',2352 --CHILD_ORG_ID

,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)

,3084 --TEMPLATE_ID 

FROM XXFIA_ITEM_INTERFACE_NS 

WHERE ITEM_CODE <> 'NG000016'

AND CATEGORY_ID IS NOT NULL

--AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)

COMMIT;

6.concurrent Program.

-----------------------------------------------Data Fix to assign items to child org-------------------

Update 2398 to your master org 

REM datafix after uploading master items_before_assign_to_Other_orgs

REM take backup of mtl_systems_items_b before running this script REM $Header: b19630365.sql 120.0.12010000.1 2014/10/28 12:08:24 liswang noship $

REMREM dbdrv sql ~PROD ~PATH ~FILE none none none sqlplus \ 

REM dbdrv phase=upg checkfile:~PROD:~PATH:~FILE 

REM +======================================================================+ 

REM |                Copyright (c) 1997, 2013 Oracle Corporation           | 

REM |                   Redwood Shores, California, USA                    | 

REM |                        All rights reserved.                          | 

REM +======================================================================+ 

REM SQL Script File Name: 

REM        b19630365.sql

REMREM Description: 

REM        This sql script updates item attributes with   

REM        R12 default value for all items. 

REMREM ======================================================================== set verify off

WHENEVER SQLERROR EXIT FAILURE ROLLBACK; 

WHENEVER OSERROR EXIT FAILURE ROLLBACK; Update mtl_system_items_b

set CATALOG_STATUS_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  CATALOG_STATUS_FLAG is null;COMMIT;Update mtl_system_items_b

set COLLATERAL_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  COLLATERAL_FLAG is null;COMMIT;Update mtl_system_items_b

set CONTAINER_ITEM_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  CONTAINER_ITEM_FLAG is null;COMMIT;Update mtl_system_items_b

set DOWNLOADABLE_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  DOWNLOADABLE_FLAG is null;COMMIT;Update mtl_system_items_b

set ELECTRONIC_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  ELECTRONIC_FLAG is null;COMMIT;Update mtl_system_items_b

set EVENT_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  EVENT_FLAG is null;COMMIT;Update mtl_system_items_b

set INDIVISIBLE_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  INDIVISIBLE_FLAG is null;COMMIT;Update mtl_system_items_b

set MRP_CALCULATE_ATP_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  MRP_CALCULATE_ATP_FLAG is null;COMMIT;Update mtl_system_items_b

set VEHICLE_ITEM_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  VEHICLE_ITEM_FLAG is null;COMMIT;Update mtl_system_items_b

set RECIPE_ENABLED_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  RECIPE_ENABLED_FLAG is null;COMMIT;Update mtl_system_items_b

set BOM_ENABLED_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  BOM_ENABLED_FLAG is null;COMMIT;Update mtl_system_items_b

set LOT_STATUS_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_STATUS_ENABLED is null;COMMIT;Update mtl_system_items_b

set SERIAL_STATUS_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  SERIAL_STATUS_ENABLED is null;COMMIT;Update mtl_system_items_b

set LOT_DIVISIBLE_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_DIVISIBLE_FLAG is null;COMMIT;Update mtl_system_items_b

set LOT_SPLIT_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_SPLIT_ENABLED is null;COMMIT;Update mtl_system_items_b

set LOT_MERGE_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_MERGE_ENABLED is null;COMMIT;Update mtl_system_items_b

set LOT_TRANSLATE_ENABLED = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  LOT_TRANSLATE_ENABLED is null;COMMIT;

Update mtl_system_items_b

set BULK_PICKED_FLAG = 'N'

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  BULK_PICKED_FLAG is null;COMMIT;

COMMIT;Update mtl_system_items_b

set EQUIPMENT_TYPE = 2

WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND  EQUIPMENT_TYPE is null;COMMIT;EXIT;