, ,

API – Supplier & Supplier Site Creation

declare
        l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
        l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
        x_return_status VARCHAR2(2000);
        x_msg_count NUMBER;
        x_msg_data VARCHAR2(2000);
        l_upd number := 0;
        x_vendor_id number;
        x_party_id number;
        x_vendor_site_id number;
        x_party_site_id number;
        x_location_id number;
        l_party_site_id number;
        l_organization_id number;
begin
     –mo_global.set_policy_context(‘S’,1650);
          l_vendor_rec.SEGMENT1     := ‘1234577’;
          l_vendor_rec.VENDOR_NAME  := ‘TESTSUPPLIER’;
          l_vendor_rec.SUMMARY_FLAG := ‘N’;
          l_vendor_rec.ENABLED_FLAG := ‘Y’;
         

 AP_VENDOR_PUB_PKG.Create_Vendor
            ( p_api_version    => 1,
              x_return_status  => x_return_status,
              x_msg_count      => x_msg_count,
              x_msg_data       => x_msg_data,
              p_vendor_rec     => l_vendor_rec,
              x_vendor_id      => x_vendor_id,
              x_party_id       => x_party_id
            );

            if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in supplier creation!!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);

                    IF x_msg_count > 1 THEN
                        FOR i IN 1..x_msg_count LOOP
                             dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                        END LOOP;
                    END IF;
            else
            l_vendor_site_rec.vendor_id            := x_vendor_id;
            l_vendor_site_rec.VENDOR_SITE_CODE     := ‘HOME’;
            l_vendor_site_rec.org_id               := 1650;
            l_vendor_site_rec.COUNTRY              := ‘AE’;                
            l_vendor_site_rec.ADDRESS_LINE1        :=’Abu Dhabi’;
            l_vendor_site_rec.PURCHASING_SITE_FLAG := ‘Y’;   
            l_vendor_site_rec.PAY_SITE_FLAG        := ‘Y’;   
             

 AP_VENDOR_PUB_PKG.Create_Vendor_Site
            ( p_api_version    => 1,
            x_return_status    => x_return_status,
            x_msg_count        => x_msg_count,
            x_msg_data         => x_msg_data,
            p_vendor_site_rec  => l_vendor_site_rec,
            x_vendor_site_id   => x_vendor_site_id,
            x_party_site_id    => x_party_site_id,
            x_location_id      => x_location_id
            );

            if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in supplier site creation!!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);
                            IF x_msg_count > 1 THEN
                                FOR i IN 1..x_msg_count LOOP
                                        dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                                END LOOP;
                            END IF;
            ELSE
                        dbms_output.put_line(‘Supplier Site Created!!!’);

            end if; 
           end if;  
end;

/by
, ,

API – AR Cash Receipt

CREATE OR REPLACE PROCEDURE APPS.XX_CREATE_CASH_RECEIPT_APPLY(errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2)
IS
L_RETURN_STATUS VARCHAR2(240);
L_MSG_COUNT NUMBER;
L_MSG_DATA VARCHAR2(240);
L_CASH_RECEIPT_ID NUMBER;
v_customer_number VARCHAR2(240);
v_cust_name VARCHAR(240);
v_amount NUMBER;
v_receipt_number NUMBER;



CURSOR C1
IS
SELECT * FROM XX_AR_RECEIPTS_GMC;

BEGIN

BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,150);
END;


FOR I IN C1 LOOP

BEGIN

v_cust_name := I.customer_name; –substr(I.CUSTOMER_NAME,1,length(I.CUSTOMER_NAME)-1);


SELECT DISTINCT ARC.CUSTOMER_NUMBER
INTO v_customer_number
FROM AR_CUSTOMERS ARC
,HZ_CUST_ACCOUNTS_ALL HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = ARC.CUSTOMER_ID
AND HCAS.ORG_ID = 150
AND LTRIM(RTRIM(UPPER(ARC.CUSTOMER_NAME))) = LTRIM(RTRIM(UPPER(v_cust_name)));


DBMS_OUTPUT.PUT_LINE (‘Customer Id – ‘||v_customer_number);


EXCEPTION
WHEN NO_DATA_FOUNd THEN
DBMS_OUTPUT.PUT_LINE(I.CUSTOMER_NAME||’ Customer Error: ‘||SUBSTR(SQLERRM,1,150));
END;


v_amount:= to_number(substr(I.AMOUNT,1,length(I.AMOUNT)-1));
–v_amount := to_number(I.AMOUNT);
v_receipt_number := to_number(I.RECEIPT_NUMBER);


AR_RECEIPT_API_PUB.create_cash
( p_api_version => ‘1.0’,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => ‘AED’,
p_amount => v_amount,
p_receipt_number => v_receipt_number,
p_receipt_date => sysdate,
p_gl_date => to_date(’31-dec-2008′),
p_customer_number => v_customer_number,
p_org_id => 150,
p_receipt_method_id => ‘2007’,
p_cr_id => l_cash_receipt_id);



/* AR_RECEIPT_API_PUB.create_and_apply
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_amount => v_amount,
p_receipt_number => v_receipt_number,
p_receipt_date => sysdate,
p_gl_date => to_date(’31-dec-2008′),
p_customer_number => v_customer_number,
p_location => ‘Abu Dhabi’,
p_receipt_method_id => ‘2007’,
p_trx_number => ‘500001’,
p_cr_id => l_cash_receipt_id
);
*/


END LOOP;


DBMS_OUTPUT.PUT_LINE(‘Cash Receipt Created & Applied’||’-‘||l_cash_receipt_id||’- Comments : ‘||l_msg_data||l_return_status);


COMMIT;


EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,150)||’-‘||l_msg_data);

END;
/
/by
,

API – Inventory Item Category Combination Creation

CREATE OR REPLACE PROCEDURE APPS.xx_create_new_item_category
IS
   l_category_rec    inv_item_category_pub.category_rec_type;
/* 
            Query to check results 
            SELECT  M1.SEGMENT1||'.'||M1.SEGMENT2||'.'||M1.SEGMENT3||'.'||M1.SEGMENT4||'.'||M1.SEGMENT5||'.'||M1.SEGMENT6
            FROM    MTL_CATEGORIES_B M1,
                    MTL_CATEGORIES_TL M2
            WHERE   M1.CATEGORY_ID = M2.CATEGORY_ID
            AND     M2.LANGUAGE = 'US'
            
            
            Table  Structure 
          CREATE TABLE XX_CREATE_NEW_CATEGORY
            (
              STRUCTURE_ID  NUMBER,
              SEGMENT1      VARCHAR2(2000 BYTE),
              SEGMENT2      VARCHAR2(2000 BYTE),
              SEGMENT3      VARCHAR2(2000 BYTE),
              SEGMENT4      VARCHAR2(2000 BYTE),
              SEGMENT5      VARCHAR2(2000 BYTE),
              SEGMENT6      VARCHAR2(2000 BYTE)
            )

*/
   CURSOR c_cat
   IS
   SELECT *
   FROM xx_create_new_category;

   l_api_version     NUMBER;
   o_return_status   VARCHAR2 (2000);
   o_msg_count       NUMBER;
   o_msg_data        VARCHAR2 (2000);
   o_errorcode       VARCHAR2 (2000);
   l_error_code      VARCHAR2 (2000);
   l_error_desc      VARCHAR2 (2000);
   l_conv_status     VARCHAR2 (2000);
   v_category_id     NUMBER;
   
   
BEGIN
fnd_global.apps_initialize (1130, 50634, 401);

   FOR i IN c_cat
   LOOP
      l_category_rec := NULL;
      l_category_rec.structure_id := i.structure_id;   -- inventory super user > setup > items > categories > Query The Category Set > get the Struture id from Examine
      --   l_category_rec.structure_code := g_cat_flex_code;
      l_category_rec.summary_flag := 'N';
      l_category_rec.enabled_flag := 'Y';
      l_category_rec.segment1 := TO_CHAR (i.segment1);
      l_category_rec.segment2 := TO_CHAR (i.segment2);
      l_category_rec.segment3 := TO_CHAR (i.segment3);
      l_category_rec.segment4 := TO_CHAR (i.segment4);
      l_category_rec.segment5 := TO_CHAR (i.segment5);
      l_category_rec.segment6 := TO_CHAR (i.segment6);
      --
      -- After the category record is loaded, then call the create_category api to
      -- create the new mtl_categories record.
      inv_item_category_pub.create_category
                                       (p_api_version        => 1.0,
                                                              --l_api_version,
                                        p_init_msg_list      => fnd_api.g_false,
                                        p_commit             => fnd_api.g_true,
                                        x_return_status      => o_return_status,
                                        x_errorcode          => o_errorcode,
                                        x_msg_count          => o_msg_count,
                                        x_msg_data           => o_msg_data,
                                        p_category_rec       => l_category_rec,
                                        x_category_id        => v_category_id
                                       );       --l_category_rec.category_id);

      IF o_return_status = 'S'
      THEN
         DBMS_OUTPUT.put_line ('*****************');
         DBMS_OUTPUT.put_line (   'Category successfully created. New Category Id = '|| v_category_id);
         DBMS_OUTPUT.put_line (   'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5|| '.'|| i.segment6);
         DBMS_OUTPUT.put_line ('*****************');
      ELSE
         l_error_code := NVL (l_error_code, '') || 'CATCONV005,';
         l_error_desc := NVL (l_error_desc, '') || '#API Error while creating Category';
         l_conv_status := 'ERROR';
         DBMS_OUTPUT.put_line ('API STATUS : ' || o_return_status);
         DBMS_OUTPUT.put_line (   'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5
                               || '.'|| i.segment6|| SQLERRM);

         IF o_msg_count > 0
         THEN
            DBMS_OUTPUT.put_line (o_errorcode);
            DBMS_OUTPUT.put_line (o_msg_data);

            FOR i IN 1 .. o_msg_count
            LOOP
               DBMS_OUTPUT.put_line
                      (   i|| '.'|| SUBSTR(fnd_msg_pub.get (p_encoded      => fnd_api.g_false),1,255));
            END LOOP;
         END IF;
      END IF;
   END LOOP;
END;
/

begin
xx_create_new_item_category;
end;
/by
, ,

API – Update Contact details of Contact Person(Suppliers)

declare
 l_contact_point_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
 l_email_rec         HZ_CONTACT_POINT_V2PUB.email_rec_type;
 l_contact_point_id  NUMBER;
 x_return_status VARCHAR2(2000);
 x_msg_count NUMBER;
 x_msg_data VARCHAR2(2000);
begin
    l_contact_point_rec.owner_table_id := 226285;                
    l_contact_point_rec.contact_point_type :=’EMAIL’;
    l_contact_point_rec.owner_table_name := ‘HZ_PARTIES’;
    l_contact_point_rec.created_by_module :=’POS_SUPPLIER_MGMT’;
    l_email_rec.email_address := ‘[email protected]‘ ;
 
HZ_CONTACT_POINT_V2PUB.create_email_contact_point
   (
    p_contact_point_rec =>  l_contact_point_rec,  
    p_email_rec         =>  l_email_rec, 
    x_contact_point_id  =>  l_contact_point_id,        
    x_return_status     =>  x_return_status,
    x_msg_count         =>  x_msg_count,
    x_msg_data          =>  x_msg_data
  );
  if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in supplier contact !!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);
                            IF x_msg_count > 1 THEN
                                FOR i IN 1..x_msg_count LOOP
                                        dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                                END LOOP;
                            END IF;
            ELSE
                        dbms_output.put_line(‘Supplier Contact updated !!!!!!’||l_contact_point_id);

            end if; 
end;

/by
, ,

API – AR Invoice (Transaction) Creation

CREATE OR REPLACE procedure APPS.xx_ar_invoice_api
is
        l_return_status         varchar2(1);
        l_msg_count             number;
        l_msg_data              varchar2(2000);
        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_cust_trx_id           number;

BEGIN
        
        begin
            MO_GLOBAL.SET_POLICY_CONTEXT('S',82);
        end;
        
        fnd_global.apps_initialize(1090,20678,222);

        l_batch_source_rec.batch_source_id          :=  1001;
        l_trx_header_tbl(1).trx_header_id           :=  9898;
        l_trx_header_tbl(1).trx_date                :=  sysdate;
        l_trx_header_tbl(1).trx_currency            :=  'AED';
        l_trx_header_tbl(1).cust_trx_type_id        :=  1000;
        l_trx_header_tbl(1).bill_to_customer_id     :=  1139;
        l_trx_header_tbl(1).term_id                 :=  1000;
        l_trx_header_tbl(1).finance_charges         :=  'N';
        l_trx_header_tbl(1).status_trx              :=  'OP';
        l_trx_header_tbl(1).printing_option         :=  'NOT';
        --l_trx_header_tbl(1).reference_number            :=  '1111';
        l_trx_lines_tbl(1).trx_header_id            :=  9898;
        l_trx_lines_tbl(1).trx_line_id              :=  101;
        l_trx_lines_tbl(1).line_number              :=  1;
        l_trx_lines_tbl(1).inventory_item_id        :=  1185;
       -- l_trx_lines_tbl(1).description              :=  'CAST IRON GRILL-325*485MM';
        l_trx_lines_tbl(1).quantity_invoiced        :=  3;
        l_trx_lines_tbl(1).unit_selling_price       :=  525;          --Price
        l_trx_lines_tbl(1).uom_code                 :=  'EAC';
        l_trx_lines_tbl(1).line_type                :=  'LINE';
        l_trx_dist_tbl(1).trx_dist_id               :=  101;
        l_trx_dist_tbl(1).trx_line_id               :=  101;
        l_trx_dist_tbl(1).ACCOUNT_CLASS             := 'REV';
        l_trx_dist_tbl(1).percent                   := 100;
        l_trx_dist_tbl(1).CODE_COMBINATION_ID       := 1012;
        
--Here we call the API to create Invoice with the stored values


    AR_INVOICE_API_PUB.create_invoice
    (p_api_version          => 1.0
    --,p_commit               => 'T'
    ,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_return_status        => l_return_status
    ,x_msg_count            => l_msg_count
    ,x_msg_data             => l_msg_data
    );
    
    dbms_output.put_line('Created:'||l_msg_data||l_return_status);

    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(l_return_status||':'||sqlerrm);
    Else 
        dbms_output.put_line(l_return_status||':'||sqlerrm);
        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);
            Dbms_output.put_line('customer_trx_id: ' || l_cust_trx_id);
        Else
            Dbms_output.put_line(sqlerrm);
        End If;
    end if;
    commit;
End;
/
/by