Oracle Apps R12 api to Create AR Invoice from existing AR Invoice

DECLARE
V_OUT NUMBER;
BEGIN
 XXCRT_ARINV_FRM_OLDINV(8173483,8777389);
DBMS_OUTPUT.PUT_LINE(V_OUT);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


create or replace procedure XXCRT_ARINV_FRM_OLDINV
(
P_CUSTOMER_ID NUMBER,
P_CUSTOMER_TRX_ID NUMBER
--,P_CUSTOMER_TRX_ID_OUT OUT NUMBER
)
 as
--DECLARE
    P_RESULT                  VARCHAR2 (300);
    P_DATE                    DATE := TRUNC(SYSDATE);
    l_batch_source_id         NUMBER := 1003;                 --Source
    l_cust_trx_type_id        NUMBER := 1001;                --type_id
    v_customer_id             NUMBER := P_CUSTOMER_ID;
    L_CUSTOMER_TRX_ID         NUMBER := P_CUSTOMER_TRX_ID;
    v_user_id                 NUMBER := 26099; --FND_PROFILE.VALUE('USER_ID');
    v_resp_id                 NUMBER := 50611; --FND_PROFILE.VALUE('RESP_ID');
    v_resp_appl_id            NUMBER := 222;        --fnd_global.resp_appl_id;
    v_org_id                  NUMBER := 1455; --FND_PROFILE.VALUE('ORG_ID');--1930;
    v_date                    DATE := P_DATE;
    v_trx_number              NUMBER;
    l_return_status           VARCHAR2 (1);
    l_msg_count               NUMBER;
    l_msg_data                VARCHAR2 (2000);
    l_batch_id                NUMBER;
    l_batch_source_rec        ar_invoice_api_pub.batch_source_rec_type;
    l_trx_header_tbl          ar_invoice_api_pub.trx_header_tbl_type;
    l_trx_lines_tbl           ar_invoice_api_pub.trx_line_tbl_type;
    l_trx_dist_tbl            ar_invoice_api_pub.trx_dist_tbl_type;
    l_trx_salescredits_tbl    ar_invoice_api_pub.trx_salescredits_tbl_type;
    l_trx_contingencies_tbl   ar_invoice_api_pub.trx_contingencies_tbl_type;
    trx_header_id             NUMBER;
    trx_line_id               NUMBER;
    trx_dist_id               NUMBER;
    v_line                    NUMBER := 1;
    v_line_count              NUMBER := 0;
    v_price_total             NUMBER := 0;

    l_currency_code           VARCHAR2 (100) := 'USD';
    l_site_use_id             NUMBER;
    L_EXIST                   VARCHAR2 (100);
    L_MEMO_LINE_ID            NUMBER;

    CURSOR cbatch IS
        SELECT customer_trx_id
          FROM ra_customer_trx_all
         WHERE batch_id = l_batch_id;

    CURSOR cvalidtxn IS
        SELECT trx_header_id
          FROM ar_trx_header_gt
         WHERE trx_header_id NOT IN
                   (SELECT trx_header_id FROM ar_trx_errors_gt);
                 
     CURSOR HEADER_DETAILS IS
     SELECT * FROM
     RA_CUSTOMER_TRX_ALL
     WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID     ;             

    CURSOR line_items IS
        SELECT MEMO_LINE_ID,
               UNIT_SELLING_PRICE                PRICE,
               QUANTITY_INVOICED                 QTY,
               UOM_CODE,
               (SELECT GL_ID_REV
                  FROM AR_MEMO_LINES_ALL_VL
                 WHERE     MEMO_LINE_ID = RCL.MEMO_LINE_ID
                       AND (UOM_CODE = RCL.UOM_CODE OR RCL.UOM_CODE IS NULL)
                       AND ORG_ID = V_ORG_ID)    GL_ID,
               (SELECT DESCRIPTION
                  FROM AR_MEMO_LINES_ALL_VL
                 WHERE     MEMO_LINE_ID = RCL.MEMO_LINE_ID
                       AND (UOM_CODE = RCL.UOM_CODE OR RCL.UOM_CODE IS NULL)
                       AND ORG_ID = V_ORG_ID)    NAME
          FROM RA_CUSTOMER_TRX_LINES_ALL RCL
         WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID;
       
         --end of cursors
BEGIN


    BEGIN
        SELECT ra_customer_trx_s.NEXTVAL INTO trx_header_id FROM DUAL;
    END;

    BEGIN
     
 SELECT c.site_use_id
          INTO l_site_use_id
          FROM hz_cust_accounts        a,
               hz_cust_acct_sites_all  b,
               hz_cust_site_uses_all   c,
               hz_party_sites          d,
               hz_locations            e
         WHERE     a.cust_account_id = b.cust_account_id
               AND b.cust_acct_site_id = c.cust_acct_site_id
               AND b.party_site_id = d.party_site_id
               AND d.location_id = e.location_id
               AND c.site_use_code = 'BILL_TO'
               AND c.status = 'A'
               AND b.org_id = c.org_id
               AND b.org_id = v_org_id
               AND a.cust_account_id = v_customer_id; --8173483
             
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.PUT_LINE ('ERROR IN SITE--22');
    END;
   
   
   FOR H IN HEADER_DETAILS LOOP
    fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
    mo_global.init ('AR');
    mo_global.set_policy_context ('S', v_org_id);
    xla_security_pkg.set_security_context (v_resp_appl_id);
    DBMS_OUTPUT.put_line ('l_cust_trx_type_id' || l_cust_trx_type_id);
    DBMS_OUTPUT.put_line ('bill_to_customer_id' || v_customer_id);
    DBMS_OUTPUT.put_line ('l_site_use_id' || l_site_use_id);
    l_trx_header_tbl (1).trx_header_id := trx_header_id;
    l_trx_header_tbl (1).bill_to_customer_id := v_customer_id;
    l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;
    l_trx_header_tbl (1).trx_date := TRUNC (p_date);
    l_trx_header_tbl (1).gl_date := TRUNC (p_date);
    l_trx_header_tbl (1).trx_currency := l_Currency_Code;
    l_trx_header_tbl (1).primary_salesrep_id := -3;
    l_trx_header_tbl (1).org_id := v_org_id;
    l_trx_header_tbl (1).comments := 'Test from plsql' || ' ' || SYSDATE;
    l_trx_header_tbl (1).INTERFACE_HEADER_CONTEXT := h.INTERFACE_HEADER_CONTEXT;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE1 := h.INTERFACE_HEADER_ATTRIBUTE1;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE2 := h.INTERFACE_HEADER_ATTRIBUTE2;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE3 := h.INTERFACE_HEADER_ATTRIBUTE3;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE4 := h.INTERFACE_HEADER_ATTRIBUTE4;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE5 := h.INTERFACE_HEADER_ATTRIBUTE5;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE6 :=h.INTERFACE_HEADER_ATTRIBUTE6;
    l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE7 :=h.INTERFACE_HEADER_ATTRIBUTE7;
   
     l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE12 := TO_CHAR(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13) + 1,'yyyy/mm/dd hh:mi:ss');--'2019/01/01 00:00:00';
     
     l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE13 := TO_CHAR(ADD_MONTHS(fnd_conc_date.string_to_date(h.INTERFACE_HEADER_ATTRIBUTE13),12) ,'yyyy/mm/dd hh:mi:ss');--'2019/05/13 00:00:00';
      l_trx_header_tbl (1).INTERFACE_HEADER_ATTRIBUTE14 := h.INTERFACE_HEADER_ATTRIBUTE14;
    --l_trx_header_tbl(1).attribute11:=p_Source;
    --l_trx_header_tbl(1).attribute12:=p_trx_number;
    --l_trx_header_tbl(1).attribute13:=TRUNC(SYSDATE);
    --l_trx_header_tbl(1).attribute14:=TRUNC(P_Expiration_Date);
    --l_trx_header_tbl(1).attribute15:=p_Accounting_Rule;
    --l_trx_header_tbl(1).attribute10:=p_Sub_Service;
    --l_trx_header_tbl(1).attribute7:=NVL(substr(p_business_name,0,60),'-');
    l_trx_header_tbl (1).trx_class := 'INV';
    l_trx_header_tbl (1).bill_to_site_use_id := l_site_use_id;
    l_trx_header_tbl (1).term_id := 1000;
    l_batch_source_rec.batch_source_id := l_batch_source_id;
   
    END LOOP;
   
    FOR i IN line_items
    LOOP
        BEGIN
            v_line_count := v_line_count + v_line;
            --v_price_total := v_price_total + (i.price * i.qty);
            l_trx_lines_tbl (v_line_count).trx_header_id := trx_header_id;
            l_trx_lines_tbl (v_line_count).trx_line_id :=
                ra_customer_trx_lines_s.NEXTVAL;              --trx_line_id_v;
            l_trx_lines_tbl (v_line_count).line_number := v_line_count;
            l_trx_lines_tbl (v_line_count).description := i.name;
            l_trx_lines_tbl (v_line_count).quantity_invoiced := i.qty;
            l_trx_lines_tbl (v_line_count).unit_selling_price := i.price;
            l_trx_lines_tbl (v_line_count).line_type := 'LINE';
            --l_trx_lines_tbl (1).tax_exempt_flag := 'E';
            --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE := 'E';
            --l_trx_lines_tbl (1).TAX_EXEMPT_REASON_CODE_MEANING := 'E';
            l_trx_lines_tbl (v_line_count).memo_line_id := i.memo_line_id;
            --   l_trx_lines_tbl (1).TAX_PRECEDENCE := 10;
            --   l_trx_lines_tbl (1).TAX_RATE := 10;
            l_trx_dist_tbl (v_line_count).trx_dist_id :=
                ra_cust_trx_line_gl_dist_s.NEXTVAL;
            l_trx_dist_tbl (v_line_count).trx_line_id :=
                ra_customer_trx_lines_s.CURRVAL;
            l_trx_dist_tbl (v_line_count).account_class := 'REV';
            l_trx_dist_tbl (v_line_count).PERCENT := 100;
            l_trx_dist_tbl (v_line_count).code_combination_id := i.gl_id;
            --DBMS_OUTPUT.put_line ('i value  ->' || i);
            --DBMS_OUTPUT.put_line ('line-   ' || i || ' Amt = ' || i.price);
            --DBMS_OUTPUT.put_line ('REV ID   ' || i || '  = ' || i.gl_id);
            END;
        END LOOP;

        --END;
       
            fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
            mo_global.set_policy_context ('S', v_org_id);

            AR_INVOICE_API_PUB.create_single_invoice (
                p_api_version            => 1.0,
                P_INIT_MSG_LIST          => FND_API.G_FALSE,
                P_COMMIT                 => FND_API.G_FALSE,
                p_batch_source_rec       => l_batch_source_rec,
                p_trx_header_tbl         => l_trx_header_tbl,
                p_trx_lines_tbl          => l_trx_lines_tbl,
                p_trx_dist_tbl           => l_trx_dist_tbl,
                p_trx_salescredits_tbl   => l_trx_salescredits_tbl,
                x_customer_trx_id        => l_customer_trx_id,
                x_return_status          => l_return_status,
                x_msg_count              => l_msg_count,
                x_msg_data               => l_msg_data);
             commit;
                    DBMS_OUTPUT.put_line (l_return_status||'-'||l_customer_trx_id);
   DBMS_OUTPUT.put_line (l_msg_data);
   P_RESULT:= l_return_status||'-'||l_msg_data;
   --P_CUSTOMER_TRX_ID_OUT := l_customer_trx_id;
   IF    l_return_status = fnd_api.g_ret_sts_error
      OR l_return_status = fnd_api.g_ret_sts_unexp_error
   THEN
      DBMS_OUTPUT.put_line ('unexpected errors found!');
   ELSE
      FOR cvalidtxnrec IN cvalidtxn
      LOOP
         IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
         THEN
            DBMS_OUTPUT.put_line ('Invoice(s) suceessfully created!');
            DBMS_OUTPUT.put_line (   'Batch ID: '
                                  || ar_invoice_api_pub.g_api_outputs.batch_id
                                 );
            l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;
            FOR cbatchrec IN cbatch
            LOOP
               DBMS_OUTPUT.put_line (   'Cust Trx Id '
                                     || cbatchrec.customer_trx_id
                                    );
               v_trx_number := cbatchrec.customer_trx_id;
               DBMS_OUTPUT.PUT_LINE('INVOICE_ID->'||v_trx_number||'-INVOICE_NUMBER->'||trx_header_id);
               --P_INVOICE_ID := v_trx_number;
               --P_INVOICE_NUM := cbatchrec.TRX_NUMBER;--trx_header_id_v;
                /*UPDATE XX_FIA_MT
                 SET INVOICE_ID = v_trx_number , TAX_INVOICE_NO = trx_header_id_v
                 WHERE FLIGHT_REPORT_NO = P_FLIGHT_REPORT_NO;*/
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line ('Errors found!');
       
         END IF;
      END LOOP;
   END IF;
   COMMIT;
END;

Oracle Apps R12 Customer Phone Number, Email and AR Invoice Details Query

SELECT
CUSTOMER_TRX_ID,SET_OF_BOOKS_ID,TRX_NUMBER,
TRX_DATE,
BILL_TO_CUSTOMER_ID,
fnd_conc_date.string_to_date(INTERFACE_HEADER_ATTRIBUTE12)ST_DT,
fnd_conc_date.string_to_date(INTERFACE_HEADER_ATTRIBUTE13)END_DT,
SUBSTR(INTERFACE_HEADER_ATTRIBUTE13,1,10) END_DATE,
INTERFACE_HEADER_ATTRIBUTE14,
RAC.ORG_ID,PARTY_NAME, ACCOUNT_NUMBER,
(select
     hcp.PHONE_COUNTRY_CODE||hcp.phone_number phone_number
  from apps.hz_cust_accounts hca
     , apps.hz_parties       hp
     , apps.hz_contact_points hcp
 where hca.party_id          = hp.party_id
   and hp.party_id          = hcp.owner_table_id
   and hcp.owner_table_name = 'HZ_PARTIES'
   and CONTACT_POINT_PURPOSE = 'BUSINESS'
   and hcp.contact_point_type = 'PHONE'
   and hca.CUST_ACCOUNT_ID = BILL_TO_CUSTOMER_ID
   )phone_number,
(select
     hcp.EMAIL_ADDRESS
  from apps.hz_cust_accounts hca
     , apps.hz_parties       hp
     , apps.hz_contact_points hcp
 where hca.party_id          = hp.party_id
   and hp.party_id          = hcp.owner_table_id
   and hcp.owner_table_name = 'HZ_PARTIES'
   and CONTACT_POINT_PURPOSE = 'BUSINESS'
   and hcp.contact_point_type = 'EMAIL'
    and hca.CUST_ACCOUNT_ID = BILL_TO_CUSTOMER_ID)Email_Address
FROM  RA_CUSTOMER_TRX_ALL RAC
      ,apps.hz_cust_accounts hca
     , apps.hz_parties       hp
WHERE
hca.CUST_ACCOUNT_ID = RAC.BILL_TO_CUSTOMER_ID
AND hca.party_id          = hp.party_id
AND RAC.ORG_ID = 145
AND RAC.CUST_TRX_TYPE_ID = 1001 --type_id ???????
AND RAC.BATCH_SOURCE_ID = 1003 --Source ???????
AND RAC.INTERFACE_HEADER_ATTRIBUTE12 IS NOT NULL --ST_DT
AND RAC.INTERFACE_HEADER_ATTRIBUTE13 IS NOT NULL --END_DT
AND TRX_NUMBER = '61843'

Oracle Apps R12 Workflow Mailer --> Remove the Actions button(Approve/Reject) from the Email

 Remove the Actions button(Approve/Reject) from the Email

Solution:
Open the wtf file in workflow Builder
Right click and add attribute to the message.
Attribute: #WFM_OPEN_MAIL
Default Value: WFMAIL:OPEN_MAIL_OUTLOOK

Save and retest. 

Oracle apps r12 Workflow Mailer --> EBS FYI notification closed automatically when email is fired.

EBS notification closed automatically when email is fired.

Solution:

OAM --> Notification Mailer / Edit / Advanced / Go to Step 4 (Message Generation).

Uncheck Autoclose FYI

Oracle Apps R12 api to create organization , assign organization classification

Using Cursor................

declare
   l_organization_id number ;
   l_object_version_number number;
   l_object_version_number2 number;
   l_org_information_id number;
   cursor c1 is
   select a org_name, b location_id,c type
   from testa
   ;
begin
for i in c1 loop
apps.hr_organization_api.create_organization
                     (p_validate                    => NULL,
                      p_effective_date              => sysdate,
                      p_language_code               => apps.hr_api.userenv_lang,
                      p_business_group_id           => 80,
                      p_date_from                   => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
                      p_name                        => i.org_name,
                      p_location_id                 => i.location_id,
                      --p_date_to                     => sysdate,
                      p_internal_external_flag          => 'INT',
                      --p_internal_external_flag      => NULL,
                      p_internal_address_line       => NULL,
                      p_type                        => i.type,
                      p_comments                    => NULL,
                      p_attribute_category          => NULL,
                      p_attribute1                  => NULL,
                      p_organization_id             => l_organization_id,
                      p_object_version_number       => l_object_version_number
                      );
                      dbms_output.put_line(l_organization_id);
                      commit;
                              begin
                                 apps.hr_organization_api.create_org_classification
                                 (p_validate                   => NULL,
                                  p_effective_date             => sysdate,
                                  p_organization_id            => l_organization_id,
                                  p_org_classif_code           =>'HR_ORG',
                                  p_org_information_id         => l_org_information_id,
                                  p_object_version_number      => l_object_version_number2
                                                           );
                                exception when others then
                                dbms_output.put_line(sqlerrm);
                                end;     
                      end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end;                     
   

Create single organization, assigning classification.

declare
ln_business_group_id number := 81;
 l_organization_name varchar2(200):='TestAfzal_API' ;
  l_organization_id number ;
   l_object_version_number number;
   l_org_type number := 60;
   ln_location_id number := 142;
begin
apps.hr_organization_api.create_organization
                     (p_validate                    => NULL,
                      p_effective_date              => sysdate,
                      p_language_code               => apps.hr_api.userenv_lang,
                      p_business_group_id           => 81,
                      p_date_from                   => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
                      p_name                        => l_organization_name,
                      p_location_id                 => ln_location_id,
                      --p_date_to                     => sysdate,
                      p_internal_external_flag          => 'INT',
                      --p_internal_external_flag      => NULL,
                      p_internal_address_line       => NULL,
                      p_type                        => l_org_type,
                      p_comments                    => NULL,
                      p_attribute_category          => NULL,
                      p_attribute1                  => NULL,
                      p_organization_id             => l_organization_id,
                      p_object_version_number       => l_object_version_number
                      );
                      dbms_output.put_line(l_organization_id);
exception when others then
dbms_output.put_line(sqlerrm);
end;                     
   

declare
 l_organization_id number := 2031;
 l_org_information_id number:= 88302;
 l_org_classif_code varchar2(20) := 'HR_ORG';
 l_object_version_number number;
begin
apps.hr_organization_api.create_org_classification
                         (p_validate                   => NULL,
                          p_effective_date             => sysdate,
                          p_organization_id            => l_organization_id,
                          p_org_classif_code           =>l_org_classif_code,
                          p_org_information_id         => l_org_information_id,
                          p_object_version_number      => l_object_version_number
                           );
exception when others then
dbms_output.put_line(sqlerrm);
end;     

select * from HR_ORGANIZATION_INFORMATION_V
where organization_id in(2030,2031)

select * from hr_all_organization_units
where organization_id in(2030,2031)

Oracle Apps R12 SSHR query to check final action on a transaction

SELECT ACTION FROM PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_ITEM_KEY ='161680'
AND CREATION_DATE = (SELECT MAX(CREATION_DATE) FROM  PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_ITEM_KEY ='161680')

Query for R12 Scheduled concurrent Programs

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;