Form Commands

For compiling a form in the application,we need to set the forms path

Eg. in 11i

Login in host(unix)

pwd ->to find the path
cd $folder_name->to move to that folder
ls->to list the folders in the current path


set path

$cd $AU_TOP/forms/US

Compile Command

$f60gen module= XXFORM.fmb module_type=form output_file=$XXCUST_TOP/forms/US/XXFORM.fmx userid=apps/apps11emrmd module_type=form batch=yes compile_all=special

Important Tables in PM

PN_LEASES_ALL

PN_LEASE_DETAILS_ALL
PN_LEASE_DETAILS_HISTORY

PN_LOCATIONS_ALL

PN_PROPERTIES_ALL

PN_ADDRESSES_ALL

Update HZ_Organization_units Table

CREATE OR REPLACE PROCEDURE XX_UPDATE_BRAND_NAME
AS
   L_PERSON_REC              HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   L_ORGANIZATION_REC        HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
   L_VERSION_NUMBER          NUMBER;
   L_PARTY_ID                NUMBER;
   L_PARTY_TYPE              VARCHAR2(100);
   L_DUNS_NAME               VARCHAR2 (100);
   L_ORIG_SYSTEM_REFERENCE   VARCHAR2 (100);
   X_PARTY_NUMBER            VARCHAR2 (100);
   X_PARTY_ID                NUMBER;
   X_PROFILE_ID              NUMBER;
   X_RETURN_STATUS           VARCHAR2 (1000);
   X_MSG_COUNT               NUMBER;
   X_MSG_DATA                VARCHAR2 (1000);
   L_ERROR_MESSAGE           VARCHAR2 (1000);

   CURSOR P_RECORDS
   IS
      SELECT *
        FROM XX_BRAND_NAME_STAG where comments is null;
       
BEGIN

   FOR P1 IN P_RECORDS
    LOOP
        IF P1.COMMENTS IS NULL THEN
 
        SELECT PARTY_ID, OBJECT_VERSION_NUMBER,PARTY_TYPE
            INTO L_PARTY_ID, L_VERSION_NUMBER,L_PARTY_TYPE
            FROM HZ_PARTIES
            WHERE PARTY_ID = P1.PARTY_ID;

      IF L_PARTY_TYPE = 'ORGANIZATION'
      THEN
         L_ORGANIZATION_REC.ORGANIZATION_NAME := P1.PARTY_NAME;
        -- L_ORGANIZATION_REC.CREATED_BY_MODULE :=  P1.CREATED_BY_MODULE;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_ID :=  P1.PARTY_ID;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_NUMBER := P1.PARTY_NUMBER;
       --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM_REFERENCE :=  P1.ORIG_SYSTEM_REFERENCE;
         --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM           := P_ORIG_SYSTEM;
         L_ORGANIZATION_REC.DUNS_NUMBER_C := P1.BRAND_NAME;

       
            UPDATE APPS.HZ_ORGANIZATION_PROFILES G
               SET DUNS_NUMBER_C =  P1.BRAND_NAME
             WHERE ATTRIBUTE_CATEGORY(+) = 'Organization Information'
               AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE
                   )
               AND PARTY_ID = L_PARTY_ID;
       

         HZ_PARTY_V2PUB.UPDATE_ORGANIZATION
                           (P_INIT_MSG_LIST                    => FND_API.G_TRUE,
                            P_ORGANIZATION_REC                 => L_ORGANIZATION_REC,
                            P_PARTY_OBJECT_VERSION_NUMBER      => L_VERSION_NUMBER,
                            X_PROFILE_ID                       => X_PROFILE_ID,
                            X_RETURN_STATUS                    => X_RETURN_STATUS,
                            X_MSG_COUNT                        => X_MSG_COUNT,
                            X_MSG_DATA                         => X_MSG_DATA
                           );
      -- P_DUNS_NAME =>'URBANOO' );
     
     
     
     END IF;
   UPDATE XX_BRAND_NAME_STAG SET COMMENTS='Y' WHERE PARTY_ID =P1.PARTY_ID;
    COMMIT;
     ELSE
UPDATE XX_BRAND_NAME_STAG SET COMMENTS='N' WHERE PARTY_ID =P1.PARTY_ID;
COMMIT;
   END IF;
   END LOOP;

   FOR I IN 1 .. X_MSG_COUNT
   LOOP
      L_ERROR_MESSAGE :=
            I
         || '. '
         || SUBSTR (FND_MSG_PUB.GET (P_ENCODED => FND_API.G_FALSE), 1, 255);
   --SINA_ERROR('UPDATE_PARTY', P_PARTY_TYPE || P_PARTY_NAME || ' ' || L_ERROR_MESSAGE);
   END LOOP;
 
END XX_UPDATE_BRAND_NAME;

current GL Period

select max(gl_date) from
ra_cust_trx_line_gl_dist_all

To find Responsibilities

-- Query to find the Responsibilities based on Concurrent Request set Name

SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              frsv.user_request_set_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_request_sets_vl frsv
        WHERE fr.request_group_id = frg.request_group_id
          AND fr.responsibility_id = frt.responsibility_id
          AND frg.request_group_id = frgu.request_group_id
          AND frgu.request_unit_id = frsv.request_set_id
          AND frsv.user_request_set_name LIKE 'give user request set name'
          AND frsv.request_set_name LIKE 'request set name';
       

-- Query to find the Responsibilities based on Concurrent Program Name        
     
SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              fcpt.user_concurrent_program_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_concurrent_programs_tl fcpt
        WHERE fr.responsibility_id = frt.responsibility_id
          AND fr.request_group_id = frg.request_group_id
          AND frgu.request_group_id = frg.request_group_id
          AND frgu.request_unit_id = fcpt.concurrent_program_id
          AND fcpt.LANGUAGE = 'US'
          AND frt.LANGUAGE = 'US'
          AND fcpt.user_concurrent_program_name LIKE
                                                'Your Concurrent Program Name'        

AR Tables 11i

Query for Subledger Transfer to GL

SELECT    gjjlv.period_name             "Period Name"
        , gjb.name                      "Batch Name"
        , gjjlv.header_name             "Journal Entry For"
        , gjjlv.je_source               "Source"
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit"
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit"
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
        , gjjlv.currency_code            "Currency"
        , rctype.name                    "Trx type"
        , rcta.trx_number                "Trx Number"
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , TRUNC(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
FROM    apps.GL_JE_JOURNAL_LINES_V gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ra_customer_trx_all rcta
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ra_cust_trx_types_all rctype
WHERE     gjh.period_name IN ('OCT-2008','NOV-2008')
AND       glcc.code_combination_id = gje.code_combination_id
AND       gjh.je_batch_id = gjb.je_batch_id
AND       gjh.je_header_id = gje.je_header_id
AND       gjh.period_name = gjb.default_period_name
AND       gjh.period_name = gje.period_name
AND       gjjlv.period_name = gjh.period_name
AND       gjjlv.je_batch_id = gjh.je_batch_id
AND       gjjlv.je_header_id = gjh.je_header_id
AND       gjjlv.line_je_line_num  = gje.je_line_num
AND       gjjlv.line_code_combination_id = glcc.code_combination_id
AND       gjjlv.line_reference_4 = rcta.trx_number
AND       rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND       rcta.org_id = rctype.org_id
AND       ra.customer_id = rcta.bill_to_customer_id

AR Useful Query

Query to fetch summary data*/

SELECT hc.cust_account_id          
      ,min(hc.account_number)         customer_number
      ,sum(amount_due_remaining)      amount_due_remaining
      ,sum(aps.amount_due_original)   amount_due_original
      ,count(aps.payment_schedule_id) open_invoices
  FROM ra_customer_trx_all       ra,
       ra_customer_trx_lines_all rl,
       ar_payment_schedules_all  aps,
       ra_cust_trx_types_all     rt,
       hz_cust_accounts          hc,
       hz_parties                hp,
       hz_cust_acct_sites_all    hcasa_bill,
       hz_cust_site_uses_all     hcsua_bill,
       hz_party_sites            hps_bill,
       ra_cust_trx_line_gl_dist_all rct
 WHERE ra.customer_trx_id           = rl.customer_trx_id
   AND ra.customer_trx_id           = aps.customer_trx_id
   AND ra.org_id                    = aps.org_id
   AND rct.customer_trx_id          = aps.customer_trx_id
   AND rct.customer_trx_id          = ra.customer_trx_id
   AND rct.customer_trx_id          = rl.customer_trx_id
   AND rct.customer_trx_line_id     = rl.customer_trx_line_id
   AND ra.complete_flag             = 'Y'
   AND rl.line_type                 IN ('FREIGHT', 'LINE')
   AND ra.cust_trx_type_id          = rt.cust_trx_type_id
   AND ra.bill_to_customer_id       = hc.cust_account_id
   AND hc.status                    = 'A'
   AND hp.party_id                  = hc.party_id
   AND hcasa_bill.cust_account_id   = ra.bill_to_customer_id
   AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
   AND hcsua_bill.site_use_code     = 'BILL_TO'
   AND hcsua_bill.site_use_id       = ra.bill_to_site_use_id
   AND hps_bill.party_site_id       = hcasa_bill.party_site_id
   AND hcasa_bill.status            = 'A'
   AND hcsua_bill.status            = 'A'
   AND aps.amount_due_remaining     <> 0
   AND aps.status                   = 'OP'
   GROUP by hc.cust_account_id;