create or replace

PACKAGE BODY      csx_ar_cdh_bpel_prep

AS

   FUNCTION raise_main (

      p_subscription_guid   IN              RAW,

      p_event               IN OUT NOCOPY   wf_event_t

   )

      RETURN VARCHAR2

   IS

      l_attr_group_name   VARCHAR2 (200);

      l_return            VARCHAR2 (200);

      l_key               VARCHAR2 (240)      := p_event.geteventkey ();

      l_param_list        wf_parameter_list_t := p_event.getparameterlist ();

      n_key               VARCHAR2 (240);

      v_event_s           NUMBER;

    --l_party_site_id Number;

      l_party_site_id     VARCHAR (200);

      l_party_id          VARCHAR (200);

      l_site_use_type     VARCHAR2 (1);

      l_arz_key           VARCHAR2 (30);

      l_line_number       VARCHAR2 (20);

      l_patron            VARCHAR2 (200);

      p_position          NUMBER;

      l_number            NUMBER;

      not_arz_customer    EXCEPTION;

    --PRAGMA AUTONOMOUS_TRANSACTION;

   BEGIN

    --delete from test_error;

    --commit;

    -- Insert into test_error (sequence_test,SQLCODE_TEST,SQLERRM_TEST)

    --values( '1', l_key, 'AT beginning');

    --commit;

    -- SELECT CSX_AR_CDH_WF_KEY_S.NEXTVAL INTO V_EVENT_S FROM DUAL;

      IF    (l_key LIKE 'oracle.apps.ar.hz.Organization.create%')

         OR (l_key LIKE 'oracle.apps.ar.hz.Organization.update%')

      THEN

         SELECT csx_ar_cdh_wf_key_s.NEXTVAL

           INTO v_event_s

           FROM DUAL;

 

       /* The next section short names the event so BPEL does not fail in reading the Event Key...

         Do not use periods (.) in logical processing within BPEL*/

         IF RTRIM (SUBSTR (l_key, 32, 6)) = 'create'

         THEN

            n_key := 'OrgCreate';

            l_party_id := p_event.getvalueforparameter ('PARTY_ID');

 

            SELECT TO_CHAR (csx_ar_cdh_arz_key.NEXTVAL)

              INTO l_arz_key

              FROM DUAL;

 

            INSERT INTO csx_ar_cdh_translate

                        (cdh_value, external_value, translate_type,

                         external_system

                        )

                 VALUES (l_party_id, l_arz_key, 'Customer ID Number',

                         'ARZ'

                        );

 

            apps.wf_event.addparametertolistpos ('ARZ_KEY',

                                                 l_arz_key,

                                                 p_position,

                                                 l_param_list

                                                );

         ELSE

            n_key := 'OrgUpdate';

         END IF;

 

         /* The following code check if the customer is an ARZ customer*/

         IF n_key = 'OrgUpdate'

         THEN

            l_party_id := p_event.getvalueforparameter ('PARTY_ID');

 

            SELECT NVL (TO_CHAR (hzp.attribute2), 'NULL')

              INTO l_patron

              FROM hz_parties hzp

             WHERE hzp.party_id = TO_NUMBER (l_party_id);

 

            IF l_patron = 'NULL'

            THEN

               RAISE not_arz_customer;

            END IF;

         END IF;

 

         apps.wf_event.RAISE

                            (p_event_name      => 'oracle.apps.ar.hz.cdh.bpel.arz',

                             p_event_key       => n_key || v_event_s,

                             p_parameters      => l_param_list,

                             p_send_date       => SYSDATE

                            );

      -- COMMIT;

      -- Insert into test_error (sequence_test,SQLCODE_TEST,SQLERRM_TEST)

      -- values( '1', l_key, 'After Raise');

      -- commit;

      -- END IF;

      ELSIF    (l_key LIKE 'oracle.apps.ar.hz.PartySite.create%')

            OR (l_key LIKE 'oracle.apps.ar.hz.PartySite.update%')

            OR (l_key LIKE 'oracle.apps.ar.hz.Location.update%')

      THEN

      -- Insert into test_error (sequence_test,SQLCODE_TEST,SQLERRM_TEST)

      --  values( '2', l_key, 'before select');

      --  commit;

         l_param_list := p_event.getparameterlist ();

      -- This get call has been moved to the frist line of this else if because for location updates, the param list needs to be rewritten.

 

         IF l_key LIKE

               'oracle.apps.ar.hz.Location.update%'

      --This is particular to the Location Update

         THEN

            SELECT party_site_id

              INTO l_party_site_id

              FROM hz_party_sites

             WHERE location_id =

                      TO_NUMBER (p_event.getvalueforparameter ('LOCATION_ID'));

 

            l_param_list := NULL;

            apps.wf_event.addparametertolistpos ('DNU1',

                                                 'DNU1',

                                                 p_position,

                                                 l_param_list

                                                );

            apps.wf_event.addparametertolistpos ('DNU2',

                                                 'DNU2',

                                                 p_position,

                                                 l_param_list

                                                );

            apps.wf_event.addparametertolistpos ('DNU3',

                                                 'DNU3',

                                                 p_position,

                                                 l_param_list

                                                );

            apps.wf_event.addparametertolistpos ('DNU4',

                                                 'DNU4',

                                                 p_position,

                                                 l_param_list

                                                );

            apps.wf_event.addparametertolistpos ('ATTR_GROUP',

                                                 'PartySiteMainUpdate',

                                                 p_position,

                                                 l_param_list

                                                );

            apps.wf_event.addparametertolistpos ('PARTY_SITE_ID',

                                                 l_party_site_id,

                                                 p_position,

                                                 l_param_list

                                                );

         ELSE

            l_party_site_id := p_event.getvalueforparameter ('PARTY_SITE_ID');

         END IF;

 

         BEGIN

            SELECT SUBSTR (hpsu.site_use_type, 1, 1)

              INTO l_site_use_type

              FROM hz_party_site_uses hpsu

             WHERE hpsu.party_site_id = TO_NUMBER (l_party_site_id);

         EXCEPTION

            WHEN OTHERS

            THEN

               l_site_use_type := 'Z';

         END;

 

         IF l_site_use_type IN

                  ('L', 'P', 'M', 'G', 'J') ---- BEGIN ADDRESS TYPE VALIDATION

         THEN

            SELECT csx_ar_cdh_wf_key_s.NEXTVAL

              INTO v_event_s

              FROM DUAL;

 

         /* The next section short names the event so BPEL does not fail in reading the Event Key...

            Do not use periods (.) in logical processing within BPEL*/

            IF SUBSTR (l_key, 29, 6) = 'create'

            THEN

               n_key := 'PartySiteMainCreate';

               DBMS_LOCK.sleep (3);

 

               SELECT TO_CHAR (hps.party_id)

                 INTO l_party_id

                 FROM hz_party_sites hps

                WHERE hps.party_site_id = TO_NUMBER (l_party_site_id);

 

               apps.wf_event.addparametertolistpos ('PARTY_ID',

                                                    l_party_id,

                                                    p_position,

                                                    l_param_list

                                                   );

            ELSE

               n_key := 'PartySiteMainUpdate';

            END IF;

 

         /* The following code check if the customer is an ARZ customer*/

            IF n_key = 'PartySiteMainUpdate'

            THEN

         /* The following code checks to see if there is a patron code for the site/address

            that is being processed if there is no patron no event will raise for a non ARZ customer*/

               SELECT NVL (TO_CHAR (hzp.attribute1), 'NULL')

                 INTO l_patron

                 FROM hz_party_sites hzp

                WHERE hzp.party_site_id = TO_NUMBER (l_party_site_id);

 

               IF l_patron = 'NULL'

               THEN

                  RAISE not_arz_customer;

               END IF;

            END IF;

 

            apps.wf_event.RAISE

                            (p_event_name      => 'oracle.apps.ar.hz.cdh.bpel.arz',

                             p_event_key       => n_key || v_event_s,

                             p_parameters      => l_param_list,

                             p_send_date       => SYSDATE

                            );

         ELSIF l_site_use_type = 'B'

         THEN

            BEGIN

               SELECT csx_ar_cdh_wf_key_s.NEXTVAL

                 INTO v_event_s

                 FROM DUAL;

 

               l_number := 0;

 

            --  L_NUMBER := CSX_AR_CDH_BPEL_PREP.BILL_PROFILE_CHECK(l_party_site_id);

               SELECT COUNT (a.attr_group_id)

                 INTO l_number

                 FROM hz_party_sites_ext_b a, ego_attr_groups_v egv

                WHERE a.attr_group_id = egv.attr_group_id

                  AND UPPER (egv.attr_group_name) = 'CSX_ADDR_BILLPROF_GROUP'

                  AND a.party_site_id = TO_NUMBER (l_party_site_id);

            END;

 

            IF l_number <> 0

            THEN

               n_key := 'PartySiteMainUpdate';

 

               SELECT csx_ar_cdh_wf_key_s.NEXTVAL

                 INTO v_event_s

                 FROM DUAL;

 

               apps.wf_event.RAISE

                            (p_event_name      => 'oracle.apps.ar.hz.cdh.bpel.arz',

                             p_event_key       => n_key || v_event_s,

                             p_parameters      => l_param_list,

                             p_send_date       => SYSDATE

                            );

            END IF;

         ELSE       -- END OF ADDRESS TYPE VALIDATION

            NULL;

         END IF;

      END IF;

 

      RETURN 'SUCCESS';

   EXCEPTION

      WHEN not_arz_customer

      THEN

         NULL;

         RETURN 'SUCCESS';

      WHEN OTHERS

      THEN

   /* If this function fail the event will reside in the WF_ERROR queue*/

         l_key := SQLERRM;

   -- l_ARZ_KEY := SQLCODE;

         INSERT INTO test_error

                     (sequence_test, sqlcode_test, sqlerrm_test

                     )

              VALUES ('BPEL_PREP', l_key, n_key

                     );

 

         COMMIT;

         RETURN 'FAILURE';

   END raise_main;